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 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

Displaying Auto Sequence Number

I want to write the sql query for displaying sequence number in the result..
.
E.g.
SeqNo Col1 Col2
==== === ===
1 aa aa2
2 bb bb2
3 cc cc2
The firlst column SeqNo is not the physical column of the table...It may be
a single function to retrieve the sequence number along with the result. I
know there may be a single function in SQL server to retrieve auto
sequence....
Any idea?
Thanks in advance
PeterOoSELECT ID, IDENTITY(INT,1,1) AS seq_number into #temp
FROM TableName
select * FROM #temp
DROP TABLE #temp
Hth
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||OR
SELECT count(*) RecNum,
a.ID
FROM Agent a join
Agent b
on a.ID >= b.ID
group by a.ID
order by a.ID
DishanF
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Thanks...Can we do another way? This way needs to create a temp table and
I'm wondering if it can affects the performance...
If SQL server has a function like AutoNumber(), there will be nice...
Anyway thanks...And if you have any alternative idea, I'd be appreciated.
"DishanF" wrote:

> SELECT ID, IDENTITY(INT,1,1) AS seq_number into #temp
> FROM TableName
> select * FROM #temp
> DROP TABLE #temp
> Hth
> DishanF
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

displaying all records of duplicates

HI all I have this table below and i need to get records from col1 >1 and col2 >1

col1 col2 language
1 a s
1 b p
3 c e
3 c f

SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)

with the above query it gives me this

col1 col2 expr1
3 c 2

the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

col1 col2 expr1 language
3 c 2 e
3 c 2 f

I need the result above inorder to normalize the table and bring out the language part into another table

thanksSELECT t.col1, t.col2, d.daCount, t.language
FROM table1 AS t
INNER
JOIN (
SELECT col1, col2, COUNT(*) AS daCount
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1
) as d
ON c.col1 = t.col1
AND d.col2 = t.col2|||thanks Rudy

Got it...

Display records in a particular order

Hi,
I want to display the result set of a query in a particular order.
My query looks like this -
SELECT col1,col2,col3,col4
FROM tab1
WHERE
(col2 = 1) AND (col3 = 1) AND (col4 = 5)
OR
(col2 = 1) AND (col3 = 1) AND (col4 = 3)
OR
(col2 = 1) AND (col3 = 1) AND (col4 = 1)
OR
(col2 = 2) AND (col3 = 1) AND (col4 = 4)
col1 is the primary key of tab1.
I want the result to be such that, all records with col4 value of 5
shud be displayed first, then those with col4 value of 3 , then col4 of
1 and then col4 of 4.
Problem is everytime i run the query, it gets sorted by col1
automatically.
Does anybody know how can I get the resultset in the desired order?Hi, snigs
Try something like this:
SELECT col1,col2,col3,col4 FROM tab1
WHERE col2=1 and col3=1 and col4 in (5,3,1,4)
ORDER BY CASE col4
WHEN 5 THEN 1
WHEN 3 THEN 2
WHEN 1 THEN 3
WHEN 4 THEN 4
END
or:
SELECT col1,col2,col3,col4,col5
FROM (
SELECT col1,col2,col3,col4,
CASE col4
WHEN 5 THEN 1
WHEN 3 THEN 2
WHEN 1 THEN 3
WHEN 4 THEN 4
END as col5
FROM tab1
WHERE col2=1 and col3=1 and col4 in (5,3,1,4)
) x
ORDER BY col5
Razvan|||...order by CASE col4 WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 1 THEN 3 ELSE 4 END
"snigs" <snigdhagulhati@.gmail.com> wrote in message
news:1133371494.533720.67620@.g44g2000cwa.googlegroups.com...
> Hi,
> I want to display the result set of a query in a particular order.
> My query looks like this -
> SELECT col1,col2,col3,col4
> FROM tab1
> WHERE
> (col2 = 1) AND (col3 = 1) AND (col4 = 5)
> OR
> (col2 = 1) AND (col3 = 1) AND (col4 = 3)
> OR
> (col2 = 1) AND (col3 = 1) AND (col4 = 1)
> OR
> (col2 = 2) AND (col3 = 1) AND (col4 = 4)
> col1 is the primary key of tab1.
> I want the result to be such that, all records with col4 value of 5
> shud be displayed first, then those with col4 value of 3 , then col4 of
> 1 and then col4 of 4.
> Problem is everytime i run the query, it gets sorted by col1
> automatically.
> Does anybody know how can I get the resultset in the desired order?
>|||That worked!!!
Thanks a lot for your help!!!!!!!
Raymond D'Anjou wrote:
> ...order by CASE col4 WHEN 5 THEN 1 WHEN 3 THEN 2 WHEN 1 THEN 3 ELSE 4 END
> "snigs" <snigdhagulhati@.gmail.com> wrote in message
> news:1133371494.533720.67620@.g44g2000cwa.googlegroups.com...|||"snigs" <snigdhagulhati@.gmail.com> wrote in message
news:1133463913.069320.267700@.g44g2000cwa.googlegroups.com...
> That worked!!!
> Thanks a lot for your help!!!!!!!
I like to get this kind of message back from the poster.
Not neccessary the "Thanks", although that's good too.
It's the "That worked!!!" that I like.
Glad to help you out.

Display N/A Something When Data Does Not Exist

Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
of Values
On my report I put a filter on a list filtering out the dates with
expressions. Such as = (colDate.value = myDate)
But some time myDate does not exist in the dataset, so the after the filter
the list has no results.
What i want to achieve in this case is to display an N/A. i tried something
like this in the textbox contorl inside the list
=IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
Any help is appreciated. ThanksHi,
Why not try something within your stored procedure
Select Col1(ISNULL, 'N/A') FROM TABLE
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||Is this in an actual "list" control? There is a property in the properties
panel, called "NoRows" .. you simply type the text you want to display when
there are no rows in the list. Hope that fixes your problem.
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||thanks that worked.
"isamu" wrote:
> Is this in an actual "list" control? There is a property in the properties
> panel, called "NoRows" .. you simply type the text you want to display when
> there are no rows in the list. Hope that fixes your problem.
> "gMaster" wrote:
> > Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> > of Values
> > On my report I put a filter on a list filtering out the dates with
> > expressions. Such as = (colDate.value = myDate)
> >
> > But some time myDate does not exist in the dataset, so the after the filter
> > the list has no results.
> >
> > What i want to achieve in this case is to display an N/A. i tried something
> > like this in the textbox contorl inside the list
> > =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> >
> > Any help is appreciated. Thanks