Prev: Remove Space within a String
Next: is there any way where the database is not even visible to administrator
From: Jonathan on 5 Dec 2009 06:51
Hi, using sql2005. I want to select unique records from a table.
Unfortunitely the field that is used to sort the output cannot be in the
select distinct statement. Any ideas?

Many thanks,
Jonathan


From: Dan Guzman on 5 Dec 2009 10:06
> Hi, using sql2005. I want to select unique records from a table.
> Unfortunitely the field that is used to sort the output cannot be in the
> select distinct statement. Any ideas?

If I understand correctly, you have a scenario like:

CREATE TABLE dbo.FooBar(
Foo int NOT NULL,
Bar int NOT NULL
);

INSERT INTO dbo.FooBar VALUES
(1,1),
(1,3),
(2,2);

SELECT DISTINCT Foo
FROM dbo.FooBar
ORDER BY Bar;

Of course, the above SELECT will not work because the ORDER BY expression
must be in the SELECT list when you use DISTINCT. The desired sequence
would otherwise be ambiguous.

Can you elaborate on your expected results? Maybe you could use a GROUP BY
instead of DISTINCT so that a well-defined aggregate value can be specified
in the ORDER BY:

SELECT Foo
FROM dbo.FooBar
GROUP BY Foo
ORDER BY MIN(Bar);

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
news:0E3E7D65-9770-46E2-93BE-7C006A8566A9(a)microsoft.com...
> Hi, using sql2005. I want to select unique records from a table.
> Unfortunitely the field that is used to sort the output cannot be in the
> select distinct statement. Any ideas?
>
> Many thanks,
> Jonathan
>
>
From: Dan Guzman on 5 Dec 2009 10:13
> INSERT INTO dbo.FooBar VALUES
> (1,1),
> (1,3),
> (2,2);

By the way, the INSERT script I posted uses SQL 2008 syntax but my
suggestion applies to SQL 2005 as well.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/




From: Erland Sommarskog on 5 Dec 2009 10:20
Jonathan (Jonathan(a)discussions.microsoft.com) writes:
> Hi, using sql2005. I want to select unique records from a table.
> Unfortunitely the field that is used to sort the output cannot be in the
> select distinct statement. Any ideas?

In a general perspective your request does not make sense. Consider this
data:

id City
1 London
2 Brussels
3 C�rdoba
4 Birmingham
5 London
6 Copenhagen
7 C�rdoba
8 Copenhagen
9 London

So you want:

SELECT DISTINCT City FROM tbl ORDER BY id

But what would this mean? Should London sort before or after Brussels?

What could make sense is to sort the cities by the smallest id (or
largest, sum of ids etc), which can be expressed as:

SELECT City
FROM (SELECT City, minid = MIN(id)
FROM cities
GROUP BY City) AS x
ORDER BY minid

Now, it may be that in your case there is a constraint of some sort
which makes your request sensible, but I since don't know your
query, I can't tell. There is also the possibility that you I have
really thought the whole way what you are asking for.

I would suggest that you post more information about your query.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 5 Dec 2009 10:31
You can use the ranking functions with a partition on the distinct columns and then select rank 1:

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
col1 INT,
col2 INT,
col3 INT);

INSERT INTO Foo VALUES(1, 1, 1, 1);
INSERT INTO Foo VALUES(2, 1, 1, 2);
INSERT INTO Foo VALUES(3, 1, 1, 3);
INSERT INTO Foo VALUES(4, 1, 2, 4);

SELECT col1, col2
FROM (
SELECT col1, col2, col3,
ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY col3) AS rn
FROM Foo) AS T
WHERE rn = 1
ORDER BY col3;

/*

col1 col2
----------- -----------
1 1
1 2

*/

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Remove Space within a String
Next: is there any way where the database is not even visible to administrator