Distinct - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-01-14 : 05:44:16

Doesn't work as I had anticipated.

The below doesn't return a distinct list of the column re_desc.
This seems to be because the re_id is different, but I havent selected a distinct on that field. I am confused

SELECT distinct (re_desc),
dbo.tb_room_extra.re_amount,
dbo.tb_room_extra.re_amount,
dbo.tb_room_extra.re_start,
dbo.tb_room_extra.re_end,
dbo.tb_room_extra.re_id from
tb_room_extra where
dbo.tb_room_extra.propertyid = @propertyid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 05:49:48

Since you obviously have duplicate records regarding re_desc, what are the rules for handling them?
Which record do you want to display?

Are you using SQL Server 2005? Then use this

SELECT re_desc, re_amount, re_start, re_end, re_id
from (
SELECT re_desc, re_amount, re_start, re_end, re_id, row_number() OVER (PARTITION BY re_desc ORDER BY re_id DESC) AS recID
FROM tb_room_extra WHERE propertyid = @propertyid
) AS d
where recid = 1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 08:54:05

also this if you're using sql 2000 or before


SELECT t.re_desc, 
t.re_amount, 
t.re_amount, 
t.re_start, 
t.re_end,
t.re_id from 
tb_room_extra t
join (select re_desc,max(re_id) AS maxid
      from tb_room_extra
      group by re_desc)tmp
on tmp.re_desc=t.re_desc
and tmp.maxid=t.re_id
where t.propertyid = @propertyid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources