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 |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2007-08-15 : 19:12:00
|
| Here's my query:SELECT NotYetPublished, Available, Catalogx, pubdate, pubdate_full, NYP, Fulfillment, Title from finaldata where available <> 'Out-of-Print' or (totalnew <>0 and totalused <>0 and totalcollectible<>0) order by NYP desc, pubdate_full desc, Fulfillment desc, Title I only want to return records that do not have the same Catalogx. I can't use distinct since some of the other data is different. How can I accomplish this? Thanks in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-15 : 19:16:03
|
Are you using SQL Server 2000 or SQL Server 2005?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-15 : 19:26:54
|
If you are using SQL Server 2005, you can use the ROW_NUMBER() function.SELECT NotYetPublished, Available, Catalogx, pubdate, pubdate_full, NYP, Fulfillment, Title from (SELECT NotYetPublished, Available, Catalogx, pubdate, pubdate_full, NYP, Fulfillment, Title, row_number() over (partition by catalogx) as recid from finaldata where available <> 'Out-of-Print' or (totalnew <>0 and totalused <>0 and totalcollectible<>0) ) as dwhere recid = 1order by NYP desc, pubdate_full desc, Fulfillment desc, Title
E 12°55'05.25"N 56°04'39.16" |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2007-08-15 : 19:37:13
|
| What would this look like for SQL 2000? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Help with Distinct Query