By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Trying to add a NON-DISTINCT field to a DISTINCT record set in a query.

P: n/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

Mar 12 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Mar 12 '07 #2

P: n/a
On Mar 12, 9:21 pm, "Techhead" <jorgenso...@gmail.comwrote:
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
Why can't you add admitdate in distinct . If admitdates are different
they will show
as two entries . I hope this is what you want

SELECT DISTINCT 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()))
ORDER BY zip

Mar 14 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.