Distinct Rows but All Columns

I searched but did not find the answer to my specific question...
I have a table where I need to return all columns, however, I need only
distinct rows for one of the columns. The problem is that the data
types are uniqueidentifiers.
The DISTINCT keyword works on the entire row so I cannot simply use
SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
FROM dbo.ReportingTransactions AS A
I have looked at grouping with no luck either. How can I get all
columns but distinct rows on one of the columns?
Here's my table:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
Sample Data in Table
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:30:01.123
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:42:59.200
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357
937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:14:25.857
Desired Results
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357SELECT * FROM MyTable
WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
See if that helps you.
Yosh
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Sorry, I wasn't clear. I need the column called 'SomeForeignKey' to be
distinct. Using the same basic query you suggested but with the other
column doesn't work.
SELECT * FROM MyTable WHERE SomeForeignKey in (SELECT DISTINCT
SomeForeignKey FROM MyTable)
Returns all rows...not the rows with a DISTINCT SomeForeignKey value.|||Won't this return exactly the same recordset as SELECT * FROM MYTABLE since
LASTUPDATE will *always* be in the dataset returned by (SELECT DISTINCT
LastUpdated FROM MyTable)?
"Yosh" <yoshi@.nospam.com> wrote in message
news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
> SELECT * FROM MyTable
> WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
> See if that helps you.
> Yosh
>
> <Doug@.icr-consulting.com> wrote in message
> news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>|||How would you determine which row to return? From the looks of the
desired results, what you really want is the last updated row for a
particular FK value - which is different than distinct on one column only.
-- correlated subquery
select MyPK, SomeForeignKey, LastUpdated
from mytable t1
where lastupdate = (select max(lastupdated) from mytable where
someforeignkey = t1.someforeignkey)
or
-- derived table
select t1.MyPK, t1.SomeForeignKey, t1.LastUpdated
from mytable t1
join (
select someforeignkey, max(lastUpdated) as lastupdated
from mytable
group by someforeignkey
) t2
on t1.someforeignkey = t2.someforeignkey
and t1.lastupdated = t2.lastupdated
Doug@.icr-consulting.com wrote:
> I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
>|||This comes very close:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
insert into mytable values('D301D519-BC09-411B-8F31-8EACFD2E4775',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:33:32.873')
insert into mytable values('2827DA4D-EE8F-46ED-95D2-2372F727F510',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:30:01.123')
insert into mytable values('AC1B46B6-9C85-4FD7-830D-144E573CEFF2',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:49:11.450')
insert into mytable values('E1C45075-DEEE-47CB-8E8A-CFA37EFFA377',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:47:27.967')
insert into mytable values('9EC6A9E1-BDE1-494E-9010-13D0C786557E',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:42:59.200')
insert into mytable values('D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD',
'7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C', '2005-11-11 21:38:01.543')
insert into mytable values('A46E3001-0B4C-4669-8EA6-1409CDD1FDC5',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('7AD20272-39FD-43AA-B18C-7F6D265E3962',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('CF356908-9A77-4B70-8CBD-A4221DED72FC',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:15:36.357')
insert into mytable values('937143F8-4509-400D-81D9-B19EB02F97B0',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:14:25.857')
SELECT *
FROM MYTABLE T1
WHERE LASTUPDATED = (SELECT MAX(LASTUPDATED) FROM MYTABLE T2 WHERE
T1.SOMEFOREIGNKEY = T2.SOMEFOREIGNKEY)
drop table [MyTable]
The only real problem that I see is that when there are two values with the
same SOMEFOREIGNKEY and LASTUPDATED values it still returns multiple rows.
I'd have to think about that one a bit. I think the crux of the issue here
is that there is actually nothing distinct about the record that you want to
select.
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Actually, the LastUpdated column is purely informational (as far as my
use of it). It's the SomeForeignKey column that I need to be unique.
Utlimately, I will use the SomeForeignKey column to join on another
table. Once I get the query to return the SomeForeignKey column in
distinct rows I can figure out the rest.
BTW: Thanks for you input thus far.|||Yes. You are correct.
What was I thinking.
Thanks,
Yosh
"Steve Hamilton" <shamilton@.community.nospam> wrote in message
news:OoT25Hj6FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Won't this return exactly the same recordset as SELECT * FROM MYTABLE
> since LASTUPDATE will *always* be in the dataset returned by (SELECT
> DISTINCT LastUpdated FROM MyTable)?
>
>
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
>|||If it is not the combination of SOMEFOREIGNKEY and LASTUPDATED then I am
having a hard time grasping what is distinct about the dataset that you want
returned. It sounds like what you want is one single record returned for
each distinct SomeForeignKey value in your table. The problem with that is
that multiple records exist in your table for the value and you have to in
some form or another tell sql server exactly which record to return, it is
not going to guess on your behalf It sounds like what you need to do is to
define some rule to determine which record for the particular SOMEFOREIGNKEY
value will be returned. Once you have done that crafting the query in the
syntax of what I submitted earlier should be feasible. Hope this helps.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>|||I looked at your postings and my replies and decided to try and clarify
things a bit. In your example that you originally posted you wanted the
following record in the returned result:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
In your example data the following records contain that particular
SomeForeignKey value:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:42:59.200
In this case how did you pick the particular record that you wanted to
return? Once you identify the logic to pick the specific record it should
be possible to write a query that returns the expected result. If the
particular record doesn't matter you could simply use MAX(CAST(MYKEY AS
VARCHAR(36))) to identify a single distinct record.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>