Selecting a Distinct value

Question

How to select a distinct value in a query when there are more than one column when i am doing like this i am getting an error

 select distinct tempname,rundate from History_Table ORDER BY RunDate DESC

EDIT::

i have to show like this in my gridview

Name   Rundate 
Test   DDL(to show all the rundates)
Test1  rundate

DISTINCT works on the whole set of columns. So you get duplicates tempname or rundate, but never a duplicate of the whole row. — Steve, Oct 26 '12 at 10:19
It's not clear what output you were trying to get. Could you show a sample desired output? — Walter Mitty, Oct 26 '12 at 10:24
The thing is i have to show unique name in the code behind and related rundates for that name. — Chandra sekhar, Oct 26 '12 at 10:26
@downvoters i want to know what is the reason for your downvotes if you can explain it here it would be helpful. — Chandra sekhar, Oct 26 '12 at 10:36

score 2 · Answer 1 · answered Oct 26 '12 at 10:26

2

DISTINCT give distinct rows

Seems you need tempname with one rundate (Last, First, etc). This can be achieved by using GROUP BY. Following example will give result tempname with last rundate

SELECT tempname,MAX(rundate) AS rundate FROM History_Table GROUP BY tempname

You can use ORDER BY as well.

SELECT tempname,rundate
FROM
(SELECT tempname,MAX(rundate) AS rundate  FROM History_Table GROUP BY tempname) AS SummaryTable
ORDER BY rundate

answered Oct 26 '12 at 10:26

Prasanna

4,583
2
22
29

but i need to get the all the rundates associated with the name – Chandra sekhar Oct 26 '12 at 10:35
1

So what is the wrong with your query? Its already giving it. Isn't it? – Prasanna Oct 26 '12 at 10:40

score 2 · Answer 2 · edited May 23 '17 at 12:03

2

It looks like you want to concatenate your run dates into a single string. This can be done using sql-server's xml extensions:

SELECT  t1.TempName,
        STUFF(( SELECT  ', [' + CONVERT(VARCHAR, t2.RunDate, 103) + ']'
                FROM    History_Table t2
                WHERE   t1.TempName = t2.TempName
                ORDER BY t2.RunDate
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') [RunDates]
FROM    (   SELECT  DISTINCT TempName
            FROM    History_Table 
        ) t1

Example SQL Fiddle

A full explanation of how using XML to concatenate strings is contained in another answer here, with a little help from KM's answer to another question

edited May 23 '17 at 12:03

Community

1
1

answered Oct 26 '12 at 10:57

GarethD

68,045
10
83
123

This is what i want i think will check once, can i remove the [ ] ?? – Chandra sekhar Oct 26 '12 at 11:03
removing the brackets will have no effect on the query, nor will changing the date format from 103 (`dd/MM/yyyy`), I just added it to make the result clearer. – GarethD Oct 26 '12 at 11:08
but the thing is i have to fill a label and a dropdown using this query will it work?? – Chandra sekhar Oct 26 '12 at 11:14
Is this to add all run dates into another drop down (or similar) once someone has selected a rundate? IF this is the case you either want to use 2 queries and define relations within your dataset in your code behind, or use one query and a `Dictionary>` in your code behind to handle changes in run date. You may need to update the question, or ask a new one with full details of what you are actually trying to do both on the client side and the server side. – GarethD Oct 26 '12 at 11:24
I have to bind the dates into a DDL with the associated name.Yeah will ask another question – Chandra sekhar Oct 26 '12 at 11:28
This is my new question link http://stackoverflow.com/questions/13086153/binding-dates-to-dropdownlist-inside-gridview – Chandra sekhar Oct 26 '12 at 11:35

score 0 · Answer 3 · answered Oct 26 '12 at 10:26

0

Sounds like you need two seperate querys. One to get the distinct tempnames and another to get the rundates. Your present query is correctly returning distinct combinations of the two.

answered Oct 26 '12 at 10:26

Ben Thurley

6,943
4
31
54

Prasanna's answer sounds more like what you want. It's difficult to know for sure without knowing what you want to get out of the query. – Ben Thurley Oct 26 '12 at 10:29
if i use alternative queries i am getting error in the code behind – Chandra sekhar Oct 26 '12 at 10:33

Selecting a Distinct value

3 Answers3