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 |
|
mharris
Starting Member
6 Posts |
 Posted - 2001-12-13 : 16:13:47
|
| Hellocan anyone advise on how to get rows that are distinct on 2 fields? For examplex y--- ---1 11 1 only one of these would get selectedx y--- ---1 11 2 both of these get selectedI learned by trial and error you cant doselect distinct( x,y) from whatever thanx in advance.m |
|
|
shankarc
Starting Member
37 Posts |
Posted - 2001-12-13 : 16:34:55
|
| Try Select Distinct X, Y from "your table" |
 |
|
|
mharris
Starting Member
6 Posts |
Posted - 2001-12-13 : 16:48:15
|
| I clearly have much to learn. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-13 : 17:58:33
|
quote:
I clearly have much to learn.
No worries mharris, we're here to help.  If you are used to working in Access in the past, that may account for part of the confusion. I believe in Access you could use Distinct to decide which rows to display based on only some of the fields in your select. SQL Server doesn't work that way. As shankarc points out, distinct is applied to all fields in the SELECT statement.-------------------It's a SQL thing... |
|
|
|
mharris
Starting Member
6 Posts |
Posted - 2001-12-14 : 11:51:39
|
ThanxI have another question. What do I do if I want to get rows out of a table based on 3 columns, of which the combination of 2 must be distinct, but the third can have repeats.I cant do select distinct x,y,z because that will require all three be distinct, and from trial and error i learned that select (distinct x,y),z wont work.Confused.quote:
quote:
I clearly have much to learn.
No worries mharris, we're here to help. If you are used to working in Access in the past, that may account for part of the confusion. I believe in Access you could use Distinct to decide which rows to display based on only some of the fields in your select. SQL Server doesn't work that way. As shankarc points out, distinct is applied to all fields in the SELECT statement.-------------------It's a SQL thing...
|
|
|
|
chaugner
Starting Member
9 Posts |
Posted - 2001-12-14 : 15:50:53
|
quote:
ThanxI have another question. What do I do if I want to get rows out of a table based on 3 columns, of which the combination of 2 must be distinct, but the third can have repeats.I cant do select distinct x,y,z because that will require all three be distinct, and from trial and error i learned that select (distinct x,y),z wont work.Confused.quote:
quote:
I clearly have much to learn.
No worries mharris, we're here to help. If you are used to working in Access in the past, that may account for part of the confusion. I believe in Access you could use Distinct to decide which rows to display based on only some of the fields in your select. SQL Server doesn't work that way. As shankarc points out, distinct is applied to all fields in the SELECT statement.-------------------It's a SQL thing...
You can try using a subqueryselect x, y, z from (select distinct x, y from table) t join table t2 on t.x = t2.x and t.y = t2.ythats should do it |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2001-12-14 : 16:38:13
|
In answer to both the first and second questions, what's wrong with:select x, y , zfrom tgroup by x,y,zmonoquote:
I have another question. What do I do if I want to get rows out of a table based on 3 columns, of which the combination of 2 must be distinct, but the third can have repeats.I cant do select distinct x,y,z because that will require all three be distinct, and from trial and error i learned that select (distinct x,y),z wont work.
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-14 : 23:25:33
|
| mono , your query will work asselect distinct x,y,z from tablename Harris doesnt wants that , he wants the distinct of x and y onlyChaugner's query should help him out, -------------------------"Success is when Preparedness meets Opportunity"Edited by - Nazim on 12/14/2001 23:26:06 |
|
|
|
|
|
|
|
|
"distinct" question