Prev: Remove Space within a String
Next: is there any way where the database is not even visible to administrator
From: Jonathan on 6 Dec 2009 02:57
Hi Erland, thanks for this suggestion I'll give it a go.
Many thanks,
Jonathan

"Erland Sommarskog" wrote:

> 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: Jonathan on 6 Dec 2009 02:58
Hi Plamen, thanks for this suggestion I'll give it a go.
Many thanks,
Jonathan

"Plamen Ratchev" wrote:

> 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
> .
>
From: Jonathan on 6 Dec 2009 03:04
Worked like a charm.
Thanks,
Jonathan

"Erland Sommarskog" wrote:

> 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: Jonathan on 6 Dec 2009 03:16
Hi Plamen, unfortunitely I could not make this method get the desired
outcome. However, I did appreciated exploring this approach.

Many thanks,
Jonathan

"Plamen Ratchev" wrote:

> 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
> .
>
From: Jonathan on 6 Dec 2009 03:34
Hi Plamen, yep I got it to work. Thanks.

Jonathan

"Plamen Ratchev" wrote:

> 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
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Remove Space within a String
Next: is there any way where the database is not even visible to administrator