Distinct Selection in SELECT

Hi all, How can I achieve this?
select col1,distinct(col2),col3,col4 from table1
TIAI have no idea what you are trying to achieve.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Hi all, How can I achieve this?
> select col1,distinct(col2),col3,col4 from table1
>
> TIA
>|||--Record present
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
john cvse 12/31/2002
SELECT Col1,Distinct(Col2),COl3 From Table1
-- desired recordset
Col1 Col2 Col3
mark 1qa 5/5/2003
mohan 2dc 1/1/2004
jerry cvse 12/31/2002
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> I have no idea what you are trying to achieve.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:#fgDiYlLFHA.2380@.TK2MSFTNGP10.phx.gbl...
>|||Can you explain why jerry and not john? And if it were like this:
mark 1qa 20030505
melody 1qa 20020608
jerry cvse 20021231
john cvse 20030104
What would the output be then?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
> --Record present
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
> john cvse 12/31/2002
> SELECT Col1,Distinct(Col2),COl3 From Table1
> -- desired recordset
> Col1 Col2 Col3
> mark 1qa 5/5/2003
> mohan 2dc 1/1/2004
> jerry cvse 12/31/2002
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:egU%23UalLFHA.3868@.TK2MSFTNGP10.phx.gbl...
>|||I guess sql does a max like top..or something..I am not sure
though that's what I am trying to achieve I want distinct on col2 yet want
to select the other cols too...how can I accomplish it?
TIA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uIhTtllLFHA.700@.TK2MSFTNGP10.phx.gbl...
> Can you explain why jerry and not john? And if it were like this:
> mark 1qa 20030505
> melody 1qa 20020608
> jerry cvse 20021231
> john cvse 20030104
> What would the output be then?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:OadtVhlLFHA.2384@.tk2msftngp13.phx.gbl...
>|||> I guess sql does a max like top..or something..I am not sure
> though that's what I am trying to achieve I want distinct on col2 yet
want
> to select the other cols too...how can I accomplish it?
You still haven't defined what you want to accomplish. You want one row for
every col2. However, if there are multiple different values for col1 and
col3, you need to define how SQL Server will determine which ones to
include.
If you do not care, say so. "ANY col1 / col3 will do." Maybe one of these
is what your after, though I question the usefulness of the extra columns if
this is the case:
SELECT MAX(Col1), Col2, MAX(col3)
FROM Table1
GROUP BY Col2
(Notice that Col1 will not necessarily come from the same row as Col3, which
is why I asked about the date AND the name that you wanted returned.)
If you do care, say so. Provide REAL specs (not typing out your data in
tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
full, including the link to generate insert statements) and we can provide a
real solution.
A|||great! works for me
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uhGB69lLFHA.3788@.tk2msftngp13.phx.gbl...
> want
> You still haven't defined what you want to accomplish. You want one row
for
> every col2. However, if there are multiple different values for col1 and
> col3, you need to define how SQL Server will determine which ones to
> include.
> If you do not care, say so. "ANY col1 / col3 will do." Maybe one of
these
> is what your after, though I question the usefulness of the extra columns
if
> this is the case:
> SELECT MAX(Col1), Col2, MAX(col3)
> FROM Table1
> GROUP BY Col2
> (Notice that Col1 will not necessarily come from the same row as Col3,
which
> is why I asked about the date AND the name that you wanted returned.)
> If you do care, say so. Provide REAL specs (not typing out your data in
> tabular format) as suggested in http://www.aspfaq.com/5006 (please read in
> full, including the link to generate insert statements) and we can provide
a
> real solution.
> A
>sql

distinct query for table containing records more than 69347

What would be the best way to avoid distinct clause in query to get the result.My Query looks like

select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1

ABC Contains records like

Col1 col2 col3 col4

a 1 1 1

a 2 1 1

B 2 2 2

B 1 1 1

;

;

;

how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?

Thanks

You didn't tell us what is your desired outcome...

The WHERE clause can be removed.

The GROUP BY forces what you display.

Do you wish to collapse so that all 'a'/'B', etc is in one row?

If so, then something like:

Code Snippet

SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1

|||There is no reason you need both Distinct AND group by that I know of. How is the performance of the query as you have it? And why the where (1=1)... Is this part of a larger generated query?

distinct query

Hello,
If I have data such as:
col1 col2 col3 col4(date)
1 2 3 01/11/2005
1 2 3 02/11/2005
1 2 3 03/11/2005
1 1 2 04/11/2005
1 1 2 05/11/2005
1 1 2 06/11/2005
How can I select so that the results are
1 2 3 01/11/2005
1 1 2 04/11/2005
ie, cols 1 to 3 distinct are included and the earliest date
Thank you.
S.soc wrote on Tue, 23 May 2006 17:30:38 +0100:

> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
How about
SELECT col1, col2, col3, MIN(col4) FROM data GROUP BY col1, col2, col3
you'll need to specify a sort order though, which you didn't in your post.
Dan|||You can do this with a correlated sub query:
select a.Col1, a.Col2, a.Col3, a.Col4
from SomeTable a
where col4 = (
select min(b.col4)
from SomeTable b
where b.Col1 = a.Col1
and b.Col2 = a.Col2
and b.Col3 = a.Col3
)
You can also use min in the main query, and group by the first 3 columns.
This will not work if you choose to select an additional (non-key) column,
but the first query above will. Performance wise you will have to try both
ways, but I would expect the above query to perform better, assuming you
have an index on (Col1, Col2, Col3, Col4).
Select Col1, Col2, Col3, min(Col4)
from SomeTable
group by Col1, Col2, Col3
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>|||Thank you!
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>

Distinct for different columns

Hi everebody.
I need your advice, suppose i have 4 colums
col1, col2, col3, col3
e.g.
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
aa | 1 | 2 | 1 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
so on ...
i need to distinct col2 so i my result set will be
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
it's not important which row not display
any idea' thanks in advance
Message posted via http://www.webservertalk.comWhat is the Primary Key? Please post proper DDL rather than sketches of
tables, otherwise we can only guess. Here's my guess:
SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
Depending on your key there's probably a better way. However, I'm
always suspicious of requirements that say "show me some row, I don't
care which". To me, this indicates that the data model is incorrect -
if all rows of a set are equally valid then apparently the table is
carrying redundant data, which ought to be eliminated.
David Portas
SQL Server MVP
--|||Thanks, form now u can play lotto, because u r guessed right.
However can u please explain me your examle, i'm not really understand it.
Thanks
Message posted via http://www.webservertalk.com|||SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
/* Get one row (TOP) for each value of
col2 in the outer (T1) query */
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
/* Is the row in T1 the same as the one
we got from the TOP subquery? */
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
David Portas
SQL Server MVP
--|||Thanks i got it .
P.S. the SELECT was from view and view have a lot of UNION from differents
tables, beacuse of that idon't told u what the primary key
Message posted via http://www.webservertalk.com|||Is it possible to perform exactly this funcionality without the 2 select
statements ?
Message posted via http://www.webservertalk.com|||In that case you'll probably find it much more efficient to do a join
between the base tables rather than query the view.
David Portas
SQL Server MVP
--|||>From my first post: "Please post proper DDL rather than sketches of
tables, otherwise we can only guess."
See: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I understand, but i need to use it a lot of times, that why i decided to
use view, it's big view and after i perform a lot of WHERE ... on this view
Message posted via http://www.webservertalk.com