Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vaishu
Posting Yak Master
178 Posts |
 Posted - 2008-08-10 : 16:55:48
|
| HiI have a table like belowID-----dep----cat1---cat2-----cat3----cat4---and so on1 Sto-----Mall--Shop-----door----street-2 MAF-----Ban---Shop----- ---Das3 SAP-----San--- ----- ---4 KAN----- --- -----Has --- From the above example how do I query only the category which has value and LAST in a row.ex: on row 1 I need to pick only value of cat4ex: on row 2 I need to pick only value of cat4ex: on row 3 need to pick value of cat1ex: on row 4 need to pick value of cat3I am using insert trigger so I need to read only one row at a time.So Please post any query to get the above result.Advance thanks |
|
|
pootle_flump
1064 Posts |
Posted - 2008-08-10 : 18:04:53
|
HiAssuming that the "empty" columns are NULL you can use COALESCE e.g.COALESCE(cat4, cat3, cat2, cat1) However there is a catch - this is a classic example of a first normal form violation and would typically be considered a table design problem. http://www.tonymarston.net/php-mysql/database-design.htmlCheck out first normal form in particular. Solve the first normal form issue and your SQL never needs to change. Stick with your design and, every time you add a new category, you need to change your code to account for the new column. There are other queries that will be hit by this limitation too - this is just one example. |
 |
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-08-10 : 18:29:08
|
quote:
Originally posted by pootle_flump
HiAssuming that the "empty" columns are NULL you can use COALESCE e.g.COALESCE(cat4, cat3, cat2, cat1) However there is a catch - this is a classic example of a first normal form violation and would typically be considered a table design problem. http://www.tonymarston.net/php-mysql/database-design.htmlCheck out first normal form in particular. Solve the first normal form issue and your SQL never needs to change. Stick with your design and, every time you add a new category, you need to change your code to account for the new column. There are other queries that will be hit by this limitation too - this is just one example.
HI This is temporary table and user will change the category(cat1, cat2..)most often.So some of the column is Null and some of them are empty. I tried your solution but doesn't work because of the empty columns and not null. Do you have any other idea? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-10 : 23:58:51
|
| then convert empty values to null and then apply coalesceCOALESCE(NULLIF(cat4,' '), NULLIF(cat3,' '),NULLIF(cat2,' '), NULLIF(cat1,' ')) |
|
|
|
Vaishu
Posting Yak Master
178 Posts |
Posted - 2008-08-11 : 06:58:49
|
HIThanks a lot. It worksquote:
Originally posted by visakh16
then convert empty values to null and then apply coalesceCOALESCE(NULLIF(cat4,' '), NULLIF(cat3,' '),NULLIF(cat2,' '), NULLIF(cat1,' '))
|
|
|
|
|
|
|
|
|
how do I query last colum in a row which has value