using distinct on a single table column?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

using distinct on a single table column??

Author

Topic

jneff02
Starting Member

10 Posts

Posted - 2009-07-15 : 22:40:14

Guys I have a problems with a report I'm trying to run. Here's the code...
SELECT DISTINCT
equipdaily.kequipnum, equipdaily.kmfg, equipdaily.kmodel, equipdaily.kserialnum, equipdaily.eqpsldcust, equipdaily.eqpslddate, custmast.custname,
custmast.custcity, custmast.custstate, custmast.custzip, custmast.custphone
FROM equipdaily INNER JOIN
custmast ON equipdaily.eqpsldcust = custmast.kcustnum
WHERE (equipdaily.eqpstatus = 'SO') AND (equipdaily.kmfg IN (@Mfr1, @Mfr2, @Mfr3, @Mfr4, @Mfr5)) AND (equipdaily.eqpsldcust NOT LIKE '00%')
ORDER BY custmast.custzip

My problem is that the distinct I'm using returns "distinct" rows but some of those rows all have the same equipment number. The row in it's entirety is "distinct" but it still shows multiple equipment numbers (kequipnum). Is there a way to use 'distinct' on the single column 'kequipnum'. I hope that makes sense. I can't think of a better way to explain. Let me know if some sort of better explanation is needed. Thanks in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 23:15:26

Sure, but what will be the criteria to decide which other fields (kmfg, kmodel, kserialnum etc.) to include in the result set?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-16 : 04:52:19

try to use row_number()


SELECT kequipnum,kmfg,kmodel,kserialnum,eqpsldcust,eqpslddate,custname,custcity,custstate,custzip,custphone
FROM (SELECT Row_number()OVER(PARTITION BY kequipnum ORDER BY kequipnum) AS RID, 
e.kequipnum, e.kmfg, e.kmodel, e.kserialnum, e.eqpsldcust, e.eqpslddate, c.custname, 
c.custcity, c.custstate, c.custzip, c.custphone
FROM equipdaily as e INNER JOIN
custmast ON e.eqpsldcust = c.kcustnum
WHERE (e.eqpstatus = 'SO') AND (e.kmfg IN (@Mfr1, @Mfr2, @Mfr3, @Mfr4, @Mfr5)) AND (e.eqpsldcust NOT LIKE '00%'))s
WHERE RID =1
ORDER BY custzip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources