Posted by Techhead on 03/12/07 16:21

I need to run a SELECT DISTINCT query across
multiple fields, but I need to add another field that is NON-DISTINCT
to my record set.

Here is my query:


SELECT DISTINCT lastname, firstname, middleinitial, address1,
address2, city, state, zip, age, gender
FROM gpresults
WHERE age>='18' and serviceline not in ('4TH','4E','4W')
and financialclass not in ('Z','X') and age not in
('1','2','3','4','5','6','7','8','9','0')
and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))
ORDER BY zip


This query runs perfect. No problems whatsoever. However, I need to
also include another field called "admitdate" that should be treated
as NON-DISTINCT. How do I add this in to the query?


I've tried this but doesn't work:


SELECT admitdate
FROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,
address2, city, state, zip, age, gender from gpresults)
WHERE age>='18' and serviceline not in ('4TH','4E','4W')
and financialclass not in ('Z','X') and age not in
('1','2','3','4','5','6','7','8','9','0')
and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))
ORDER BY zip


This has to be simple but I do not know the syntax to accomplish
this.


Thanks

[Back to original message]