Posted by Sylwester Lewandowski on 03/12/07 17:55

Check something like that:

SELECT lastname, firstname, middleinitial, address1,address2,city,
state, zip, age, gender, admitdate
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()))
GROUP BY lastname, firstname, middleinitial, address1,address2,city,
state, zip, age, gender
ORDER BY zip

I wonder if it solves the problem.





Techhead napisał(a):
> 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
>

• Next in forum: Re: How to Join
• Prev in forum: Re: pulling ntext values sql server 2005
• Thread view: Re: Trying to add a NON-DISTINCT field to a DISTINCT record set in a query.

[Reply to this message]