distinct values from a join

Is there any way i can get distinct values in one column from a join of 2
tables with the same columns? FOr example:
table1, column fname
frank
bob
bob
dave
frank
A distinct yields
frank
bob
dave
table2, column fname
bob
alan
dave
alan
I want to join these tables and get one column, fname, to have:
frank
bob
dave
alan
Thanks for any help.
Bernie YaegerNevermind- figured it out:
select distinct invnum from bnlsum union select distinct invnum from bnlsumr
Bernie
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eOLdvc%236FHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>|||Hey Bernie,
Just as an FYI: a UNION query performs a DISTINCT inherently. Although
your performance plan may not change much, using DISTINCT and UNION in
the same query is redundant.
If your tables are large, you may see some benefit by running SELECT
Distinct colname... UNION ALL... That way, the DISTINCT selection is
performed in parallel before the rsults are joined.
Stu|||Hi,
You also try it as
SELECT distinct fname
FROM
(
Select fname from Table1
UNION
Select fname from Table2
)UNION_TABLE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Bernie Yaeger" wrote:

> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>

DISTINCT Value

if I have 5 columns that I want to check for distinct values in all of them
(not just one columns distinct values - I want to eliminate duplicates from
the entire returned result set) will
Select DISTINCT col1,col2,col3,col4,col5 from mytable order by myfieldname
do the trick?
Do you have people in your band or group that only play by ear?
When transposing songs do those that play by ear struggle and
does it leave vocals and band members without a recording in the
correct key to practice with?
Visit http://www.jerichoband.net/jericho/keychange.htm
where you can have the song's key changed to what you need
without negatively affecting the tempo or instruments in the recording...Select DISTINCT col1,col2,col3,col4,col5 from mytable order by
myfieldname
only elimantes the combination of these columns in the resultsset, is
it that what you want to achieve ?
HTH, jens Suessmeyer.|||Just to state this right:
it only elimantes the duplicate combinations.
-Jens.|||That is exactly what DISTINCT does.
ML
http://milambda.blogspot.com/

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

DISTINCT Query

Hi,
I want a query to return the values in three columns, but I only
want distinct values in one of the three columns. Is this possible? I
want to do something like this:
SELECT A, DISTINCT(B), C
FROM TABLE Z
but SQL Server doesn't like this syntax.
Any ideas?
JDHi
No
SELECT DISTINCT A, B, C
FROM TABLE Z
How do you expect a valid set to come back with only one column being unique
and every other column an possible permutations?
You do you want your data to look like?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:fwY1f.47009$lq6.25552@.fed1read01...
> Hi,
> I want a query to return the values in three columns, but I only
> want distinct values in one of the three columns. Is this possible?
> I
> want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. The "unit of work" in a SELECT
statement is a row; if this were a file system, then the fields would
be scanned from left to right.
So, in terms of RDBMS, your question and attempted syntax make no
sense. Also TABLE is a reserved word, so code fails.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant?
CREATE TABLE Foobar
(a INTEGER NOT NULL,
b INTEGER NOT NULL UNIQUE, -- no dups allowed!
c INTEGER NOT NULL,
.);|||Joe Delphi wrote:
> Hi,
> I want a query to return the values in three columns, but I
> only want distinct values in one of the three columns. Is this
> possible? I want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
I think you will need to show us some sample data and desired results
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

DISTINCT only certain columns in on table and pull their IDs

Please help.? (I am using ASP, VB, SQL)

I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

So for example I might have?

ID 1 - Office 1 - Chicago

ID 2 - Building 2 - Chicago

So I want to show

Office 1 - Chicago (my link will contain the ID in it)

So far I have

SELECT DISTINCT City , State FROM dbo.Offices WHERE (City IS NOT NULL) AND name like 'office%' ORDER BY City ASC

This works as far as pulling unique cities and names but now I need to get those City's ID. Can I use DISTICT for certain columns? Or how do I do it so that when it does pull out the unique cities it pulls their IDs as well?

?? Select ID and (DISTINCT City , State) FROM dbo.Offices ??

Thank you.

sscoder,

are you saying that the cities id is in another table. And are you just looking for the set of distinct city, state, id 's? is so just add id into your select statement

select distinct ID, City, State FROM Offices

if this is not what you are asking, can you include a bit more info...-- jp

|||City, State, ID are in the same table.

I want to find unqiue cities and then their IDs.?I'm using the ID in the hyperlink for a string.?

If I add ID to the distinct query?it will take priority and it will not list out unique cities. So instead of displaying

Chicago
Florida
Las Vegas

It will display this if I add ID to distinct
Chicago
Chicago
Chicago
Florida
Florida
Las Vegas

I need unique cities and then their ids.? Is there a way?

Thank you.

|||i see what you are saying but it you want the distinct city and a city may have more than 1 id, then how do you know which id you want to be returned?|||would only one of the city records be of type 'office'?|||there are many cities but each office has a different name. The every main office name start with 'Office' so I only want to pull up unique cities with name starting with 'Office%'. So there will never be two same cities. Only one office per city. That's where I put "WHERE name like 'office%' "

So the list will be:

Chicago - Office 1

Florida - Office 2

Las Vegas - Office 3

The query does this already. I just can't get the ID for that one row. That's what I need help on. If that "Office 1" in "Chicago" has an ID of 2. I want to get that. How??

Distinct on only one column, return all columns

I have a table with many columns. I want to return all columns, and I want
only the first record for each distinct value in the salutation column. I
come up with the following:
select c1.* from complainer as c1
join (select distinct salutation from complainer) as c2
on c1.complainerid = c2.complainerid
The code above returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'complainerid'.
Suggestions greatly appreciated.
RandyHow do you determine "first"? I'm assuming that you are using an
IDENTITY column as your id, so try something like this:
select c1.* from complainer as c1
join (select complainerid = MIN(complainerid), salutation
from complainer GROUP BY salutation) as c2
on c1.complainerid = c2.complainerid
HTH,
Stu

DISTINCT on a single column

Hi,

I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

Example:

Name......ID

John.......1
John.......2
Mary.......3

I need only one record for John. But both Name and ID should be selected.

Thanks"I need only one record for John. But both Name and ID should be selected."

What value do you expect to be present in the ID field, given your example data?|||Any value in the ID field can be selected. It's only the name that matters.|||???

So what result to you want from:

John.......1
John.......2
Mary.......3

Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

Option C:
John, 1
, 2
Mary, 3

Your logic is not clear.

blindman|||Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

Thanks|||Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ?|||Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

Try using Limit or TOP|||You want a group by query.

Select Name, Min(ID)
from YourTable
Group By Name

blindman|||What's "Limit"?

blindman|||Here is the acual data

Name......ID...Dept..University...

John......17...A........XYZ
John......18...B........XYZ

Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

Thanks|||i think "limit" is in mysql and not in mssql ... wrong forum buddy|||Assuming ID is a unique value in your table (it better be, or your logic is not possible):

select YourTable.*
from YourTable
inner join
(select Name,
Min(ID) ID
from YourTable) DistinctIDs
on YourTable.ID = DistinctIDs.ID

This selects a single ID for each unique name and returns the data associated with that ID.

blindman|||"select Name,
Min(ID) ID
from YourTable"

Doesn't a Group By clause need to be here.|||Yeah, that would probably help... :rolleyes:

blindman

Distinct For Only Some Columns In A Row

Hello,
I'm stuck, and I have earnestly searched for an answer to this problem
during the past few hours...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
UserID MiscID
10 105
11 109
I would appreciate any assistance.
Thanks,
JimSELECT T1.UserID, Min(T2.MiscID) As MiscId
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jim Little" <123@.yahoo.com> wrote in message
news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
> Hello,
> I'm stuck, and I have earnestly searched for an answer to this problem
> during the past few hours...
> I'm trying to work with this query:
> SELECT T1.UserID, T2.MiscID
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> Based on the data in the tables, this query generates the following
> result:
> UserID MiscID
> 10 105
> 10 107
> 11 109
> 11 120
> 11 122
> The problem is that I do not want duplicate UserID's (I show only 10 and
> 11, but the table actually has many rows with duplicate UserID values).
> Instead, I want unique (distinct) UserID's and the lowest MiscID for each
> row. I would therefore like the result set to look like this:
> UserID MiscID
> 10 105
> 11 109
> I would appreciate any assistance.
> Thanks,
> Jim
>
>|||Thank you very much. That worked perfectly!
Although I made a "stripped-down" version of the problem so I could post a
focussed question, I actually need to join across four table and draw data
from each of them. However, I still want to "group" on only the first UserID
column. There are 12 additional columns that need to appear in the output.
I'll experiment a bit further and I'll post another follow-up message on
this thread if I get stuck again.
Meanwhile, thanks for the quick and helpful reply.
-- Jim
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uYpCUn8GFHA.2156@.TK2MSFTNGP09.phx.gbl...
> SELECT T1.UserID, Min(T2.MiscID) As MiscId
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> GROUP BY T1.UserID
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Jim Little" <123@.yahoo.com> wrote in message
> news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
>|||OK--I found a way to better express the problem I was having, but I need
first to add another column...
The Group By suggested by Roji does give me the following as output:
UserID MiscID
10 105
11 109
And that's what I wanted in my first question. But I had simplified my
question. In actuality, I require additional columns in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||I have posted a new message entitled "Distinct and Group By".
Thank you,
-- Jim

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

distinct count

Hi

I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.

Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).

Can some body help?

Thanks in Advance!

Santhosh

Select

Employee.EmployeeName,

Employee.EmployeeNo,

|||

Select EmployeeName, EmployeeNo, Count(*) As NoOfDays

From EmployeeShift

Where Month = @.Month And Year = @.Year

Group By EmployeeName, EmployeeNo

|||

You could create a view as

Select EmployeeName, EmployeeNo, Month, Year, Count(*) As NoOfDays

From EmployeeShift

Group By EmployeeName, EmployeeNo, Month, Year

and apply Where to it...

sql

distinct columns

Please - please -please someone help me with this one:

current table looks like this:

Act Num City

1 New York

1 Tampa

2 Denver

3 LA

3 Tampa

3 New York

Im trying to (have to ) create a table that looks like this:

Act Num City 1 City 2 City 3 --City 10

1 New York Tampa Null Null

2 Denver null null null

3 LA Tampa New York null

I new to this and am at wits end, can some one help?If you are using SQL 2005, refer to Books Online, Topic: Rotating Columns|||

Here you go..

Code Snippet

Create Table #cities (

[Act Num] Int,

[City] Varchar(100)

);

Insert Into #cities Values('1','New_York');

Insert Into #cities Values('1','Tampa');

Insert Into #cities Values('2','Denver');

Insert Into #cities Values('3','LA');

Insert Into #cities Values('3','Tampa');

Insert Into #cities Values('3','New_York');

Select

*

,Identity(int,1,1) as RowId

INTO

#Data

From

#cities

Select

[Act Num]

,Max(Case RowId When 1 Then City End) [City1]

,Max(Case RowId When 2 Then City End) [City2]

,Max(Case RowId When 3 Then City End) [City3]

,Max(Case RowId When 4 Then City End) [City4]

,Max(Case RowId When 5 Then City End) [City5]

,Max(Case RowId When 6 Then City End) [City6]

,Max(Case RowId When 7 Then City End) [City7]

,Max(Case RowId When 8 Then City End) [City8]

,Max(Case RowId When 9 Then City End) [City9]

,Max(Case RowId When 10 Then City End) [City10]

From

(

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From

#Data Main

) as data

Group By [Act Num]

|||That did it - Thank You so much!|||

Hi Manivannan,

I liked your script. I would like to add just one point in order to use the new ROW_NUMBER() function in SQL2005

So instead of

Code Snippet

select

[Act Num]

,[City]

,RowId - (Select Min(RowId)-1 From #Data Sub Where Sub.[Act Num]=Main.[Act Num]) as RowId

From #Data Main

The below script can also be used

Code Snippet

select

[Act Num]

,[City]

,ROW_NUMBER() OVER (PARTITION BY [Act Num] ORDER BY City) AS RowId

From #Data Main

Eralper

http://www.kodyaz.com

Distinct at two columns

I have this stored procedure:

ALTER PROCEDURE usp_My_Procedure
(
@.Country varchar(5)
)

AS
SELECT DISTINCT City, Short FROM Table1 WHERE Country = @.Country

RETURN

I want to select just one of each 'city' and 'short' in the database...But this is not working correct.....Whats wrong?

Lets say that I have a table that looks something like this

City Short

New York NY

Los Angeles LA

Lake Alice LA

Los Angeles LosAng

well ur code like this is anylized like give everything not repeated for these two columns,

try this

SELECT City, Short FROM Table1 WHERE Country = @.Country

and City in (SELECT DISTINCT City FROM Table1 WHERE Country = @.Country)

and Short in (SELECT DISTINCT short FROM Table1 WHERE Country = @.Country)

sql

Distinct and Group By

*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- JimWe'd need to see your DDL for the tables involved. Here's a guess:
SELECT
x.UserID, x.MiscID, t3.Age
FROM
(
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
) as x
JOIN Table2 t2 on t2.UserID = x.UserID and t2.MiscID = x.MiscID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Jim Little" <123@.yahoo.com> wrote in message
news:Ly4Ud.60429$911.14807@.fe2.texas.rr.com...
*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
In particular, you have a "magical universal" id column in both tables
and a truly vague "misc_id" in another. That is absurd, since an
identifier has to identify a particular kind of entity.
The names table1 and Table2 give us no hint as to what they mean in the
data model, either. Can we get better specs?|||OK--Fair enough. I posted the "generic" tables because I'm not authorized to
give the actual ones. However, let me create some tables that illustrate my
issue and I think it could benefit everyone.
I'll upload those very soon.
Thanks,
-- Jim|||Hi Jim
Assuming you want the lowest ImageNo per user. Two possible (untested) ways:
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
JOIN ( SELECT UserId, MIN(ImageNo) AS ImageNo FROM dbo.T_Images GROUP BY
UserId ) L ON I.UserID = L.UserID AND I.ImageNo = L.ImageNo
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
WHERE I.ImageNo = ( SELECT MIN(L.ImageNo) FROM dbo.T_Images L WHERE I.UserID
= L.UserID )
You may want to look at example data as insert statements
http://vyaskn.tripod.com/code.htm#inserts
instead of attaching data files.
John
"Jim Little" <123@.yahoo.com> wrote in message
news:Bd6Ud.61154$911.53142@.fe2.texas.rr.com...
> Hello:
> I hope this helps: I've attached an image of the tables involved. I have
> also attached a text document with a Create script (no drops are included
> for safety in case you have the same table names in your db). I pasted the
> attached Create script below for convenience (it is the same as the
> text-file attachment).
> In addition, I attached three data files, one for each data table. These
> are ASCII delimitted format.
> The query I am using is:
> SELECT dbo.T_Users.UserID AS Expr1, dbo.T_Users.FirstName AS Expr2,
> dbo.T_Users.LastName AS Expr3, dbo.T_Members.LastLogon AS Expr4,
> dbo.T_Images.Description AS Expr5,
> dbo.T_Images.ImageNo AS Expr6, dbo.T_Images.PixelHeight AS Expr7,
> dbo.T_Images.PixelWidth AS Expr8,
> dbo.T_Images.FileName AS Expr9
> FROM dbo.T_Images INNER JOIN
> dbo.T_Members ON dbo.T_Images.UserID =
> dbo.T_Members.UserID INNER JOIN
> dbo.T_Users ON dbo.T_Images.UserID =
> dbo.T_Users.UserID
> This is giving me (sorry for the word wrapping):
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Grand
> Canyon ,2,350,350,gcanyon.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Time
> Square ,3,400,600,timessq.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
> 2,Steve ,Jones ,2005-01-22 00:00:00.000,LA
> What I would like to see is:
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
>
> Notice that the results I would like to see are unque to the first (Expr1,
> or UserID) column--but I get to see all the other columns that apply to
> that row.
> One idea I had is to first create a Select that selects only the UserIDs.
> Then, use those UserIDs in an "IN" or "EXISTS" clause.
> Thank you for any insights.
> Regards,
> -- Jim
> ===============
>
> CREATE TABLE [dbo].[T_Images] (
> [ImageID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [ImageNo] [smallint] NOT NULL ,
> [Description] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PixelHeight] [smallint] NOT NULL ,
> [PixelWidth] [smallint] NOT NULL ,
> [FileName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Members] (
> [UserID] [int] NOT NULL ,
> [LastLogon] [datetime] NOT NULL ,
> [MemberAlias] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Users] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>|||Looks great--Thank you John. I will try both and I'll post feedback either
way within a day.
Thanks,
-- Jim

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

Displaying Rows as Columns

Hello All !
I am using MS-SQL Server.
I have following table :
tblExchangeData
Columns are :
------
1) Trans_Date
2) Sales
3) Purchase
4) Purchase_Brokerage
5) Sales_Brokerage
6) Branch_Name
7) ExchageSegment
There are only two ExchangeSegments : BSE & NSE
I want to calculate brokerage for ExchangeSegments.
(Brokerage=Purchase_Brokerage+Sales_Brokerage)
O/p Should be (Group By Branch):
Branch_Name BSE NSE
------------------
xx 10000 20000
.... so on

Please post the query.
Thanks in advance.

SQL Server have three none standard Aggregate functions that are not Relational Compute Sum, Cube and Rollup. The last two are super agreggate functions. Run a search for all three in the BOL(books online). Try the links below for examples. Hope this helps.

http://www.oreilly.com/catalog/wintrnssql/chapter/ch01.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_414d.asp

|||

Here is some source that will allow you to create a SProc and than you pass virtually whatever you want and the Rows Will Become Columns - Some Examples at the end of script!
You know how to configure the data set in MS RS to call a stored Procedure? (With all due respect)
CREATE PROCEDURE spPivot_Table
@.cTable varchar(80),
@.cDown varchar(80),
@.cAcross varchar(80),
@.cFunc varchar(80),
@.cAggFld varchar(80),
@.cWhere varchar(200)
As

Drop Table TempUniq
Drop Table TempPivot

Declare @.cColTtl varchar(80),
@.cSQLStr varchar(200),
@.cSQL varchar(8000),
@.nRows Int,
@.nCntr Int

-- Generate Pivot Key Table
Set @.cSQL = 'Select Distinct'+@.cAcross+' as Pivot_Value Into TempUniq From'+@.cTable+' Where'+@.cWHere+' Order By 1 '
Exec(@.cSQL)
Select IDENTITY(int, 1,1) as Pivot_Row,@.cFunc as Pivot_Func, @.cAggFld as Pivot_AggFld,@.cAcross as Pivot_Fld,@.cColTtl as Pivot_Col,Pivot_Value,@.cSQLStr as Pivot_SQL Into TempPivot From TempUniq Order By Pivot_Value

Update TempPivot Set Pivot_Col = 'Col_'+Replace(RTrim(LTrim(Convert(varchar(80),Pivot_Value))),' ','_')

-- Build and Execute Pivot SQL
Update TempPivot Set Pivot_SQL=LTrim(RTrim(Pivot_Col))+'='+Pivot_Func+'(case when '+Pivot_Fld+'=Pivot_Value and Pivot_Col='''+Pivot_Col+''' Then '+Pivot_AggFld+' else Null end)'
Select @.nRows=Max(Pivot_Row),@.nCntr=Min(Pivot_Row) From TempPivot
Set @.cSQL=''
While @.nCntr <= @.nRows
Begin
Select @.cSQL=@.cSQL+','+Pivot_SQL From TempPivot WherePivot_Row=@.nCntr
Set @.nCntr=@.nCntr+1
End

Set @.cSQL='Select'+@.cDown+','+Substring(@.cSQL,2,8000)+' From'+@.cTable+' Join TempPivot on('+@.cAcross+'=Pivot_Value) Where'+@.cWhere+' Group By'+@.cDown+' Order By'+@.cDown
Exec(@.cSQL)

EXAMPLES OF HOW TO USE -- Just execuate the SP within SQL Query Analyzer These samples use the the Northwind DB -

-- Capabilities
-- Any Combination to "Down" or By Field -- s
-- Functions Available: Sum, Avg, Min, M -- ax, Count, STD, ...
-- Across may be an expression Substring -- (ShipCountry,1,1) = Across A,B,C,D,...-- r>
-- Samples
-- Exec spPivot_Table {Table},{By Fields -- },{Across Colums},{Agg Function},{Pivot -- Field},{Filer}
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- 1=1'
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Substring(ShipCountry,1 -- ,1)','Sum','Freight','1=1'

|||hi,
can the above store procedure method be used in reporting services...because i need to generate result like the example of displaying rows as columns.|||

Angela:

Yes, I have used the SP many times within Reporting Services.

Try these steps and I think you will get the results you want.

Create the Stored Procedure - CREATE PROCEDURE spPivot_Table You can by the way name this procedure anything you want and I normally name stored procedure the same name as my reporting services report.
(This stored procedure is really executing YOUR SQL statement (Dynamic SQL) and since Dynamic SQL is being executed within the Stored Procedure ensure you eliminate ANY "white space or blanks" in your SQL Statement.
After you create the Stored Procedure open SQL Query Analyzer and construct your SQL statement - once your SQL statement works open a new window in SQL Query Analyzer and then execute the following:
Exec Stored Procedure Name 'YOUR SQL STATEMENT' and within SQL Query Analyzer you should see the results of the Stored Procedure and the cross tab created on the pivot column name. After you get the results you want from the stored procedure and your SQL statement just copy the statement in SQL Query Analyzer and then go to your report in Reporting Services.
In Reporting Services (and your report you are working on) create a data set and when creating the data set Specify COMMAND TYPE as a storedprocedure and in the QUERY STRING "Paste" your statement you copied from SQL Query Analyzer then select OK...
Test or run your data set in Reporting Services and you should see the same results that Reporting Services displays after executing the stored procedure that you would see within SQL Query Analyzer. VOILA! The fields names returned from this stored procedure are different from the names returned within reporting services with a standard SQL statement - but they are obvious once you run the data set for the first time with the pivot table stored procedure.
You can accomplish the same by using a Matrix within your report with reporting services in that the matrix is really doing the pivot table or cross tab for you but I find that it is easier to use the pivot table stored procedure.
Hope this helps!
Best Regards - Joe

|||Also, please look at the post "Limiting Matrix Columns" I provided another solution there as well.........|||Hello !
Yes, this stored procedure is very useful. I have used it many times. And performance is also good.
Thanx Joe|||

Please help. i created the SP in sql 2000 in Northwind Database. This worked. I tried to

run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
and below was the result.

Not sure if my synatx above is causing this or if there are other problems too.
Thanks

Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 11
Cannot drop the table 'TempUniq', because it does not exist in the system catalog.
Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 12
Cannot drop the table 'TempPivot', because it does not exist in the system catalog.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Where'.
Server: Msg 208, Level 16, State 1, Procedure spPivot_Table, Line 25
Invalid object name 'TempUniq'.

|||Hello:
Please execute the following statement:
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'
Your statement contained invalid field names from the table:
run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
Please see the BOLD field names above.
The first time you execute the stored procedure - you will get messages that the temp tables are not there - so a message is provided that they can't be deleted because they do not exist. Any further executions will not show those initial messages.
Also, look at the post for "Limiting Matrix Columns - I provided another script that behaves a little differently -
Hope this helps!
Best Regards,
Joe|||Hello:
Please do not take this the wrong way! Please do not use SP as a prefix for creating your stored procedures - this is a Microsoft nameing convention and things may get screwed up in the future. I always prefix my stored procedures with 'EX' for execute.
Best Reagrds,
Joe|||Run it as :
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'|||There is no problem using an "sp" prefix for a stored procedurename. The issue is with the "sp_" prefix, as SQL Server willfirst try to look in the Master database for such-named storedprocedures. I have recently read an article which explores thisissue, and the author found there is actually a negligible performancepenalty with the "sp_" prefix -- not worth the trouble to go back andrename existing stored procedures.
|||

Thanks, can i create a report using asp.net using the Stored procedure, and if so please

point me in the right direction, allowing a refresh at a push of a button or something like that ??

Thanks

Gavin

|||Hi there,
i have a table result showing like this
Code OrderName
-- ----
123 AAA
111 BBB
and i select some other fields frm another table with column name call
TestResult but i wan to display the result like this;
TestResult Para1 Para2 Para3 Para4
---- -- --- -- ---
1 123 AAA 111 BBB
2 123 AAA 111 BBB
is there anyway of geting something like the above example?
|||You want to display each and every cell as column. But what is use of it?

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from
a table, for eg if the table has 5 columns and two rows have same values for
all these five columns , the sql statement should be able to find all such
matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harshaselect col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/feat...cle.php/2235081
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harsha
select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/featu...le.php/2235081
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching rows in the table and display them to the user
How would i go about doing this
thank yo
harshaharsha,
You can code a self-join on the table.
Ex. SELECT A.* FROM mytable A JOIN mytable B ON
a.col1 = b.col1 and a.col2 = b.col2 and b.col3 = b.col3
and ....
Might be other ways, but this should work for you.
Doug
>--Original Message--
>Dear All,
> I want to write an SQL program that would
display all identical fields from a table, for eg if the
table has 5 columns and two rows have same values for all
these five columns , the sql statement should be able to
find all such matching rows in the table and display them
to the user.
>How would i go about doing this.
>thank you
>harsha
>.
>|||select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/features/mssql/article.php/2235081
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related data from a table

Dear All,
I am using a SQL server database with around 20 columns,all
the columns have numeric values, I want to write an SQL statement
which does the following:

compare each row of the table with all other rows in the table and
return all the rows that have a difference of + or - 0.5 in each
column, for eg if row1 has values 12.2,13.6,11.4,15.7 corresponding
to column1,2,3,4 the sql statement should return all the rows from the
table with values of column 1-4 between

12.2- 0.5 to 12.2 + 0.5
13.6 -0.5 to 13.6+ 0.5
11.4 -0.5 to 11.4 +0.5
15.7 -0.5 to 15.7 +0.5

so effectively this statement would search for groups of rows that
have matching values(diffence of + or - 0.5)

Could anyone suggest how i go about doing this.

thank you in advance
harshaIt's not clear to me exactly what you want to see in the result. If you just
want to *join* like rows based on the criteria you've specified then you
could do so as follows (I've had to assume a primary key because you didn't
specify one).

CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, c1 NUMERIC(3,1) NOT
NULL, c2 NUMERIC(3,1) NOT NULL, c3 NUMERIC(3,1) NOT NULL, c4 NUMERIC(3,1)
NOT NULL)

/* Sample data */
INSERT INTO SomeTable VALUES (1, 12.2, 13.6, 11.4, 15.7)
INSERT INTO SomeTable VALUES (2, 12.0, 13.9, 10.9, 15.2)

SELECT S1.keycol, S1.c1, S1.c2, S1.c3, S1.c4,
S2.keycol, S2.c1, S2.c2, S2.c3, S2.c4
FROM SomeTable AS S1
JOIN SomeTable AS S2
ON S1.keycol < S2.keycol
AND ABS(S1.c1-S2.c1) <= 0.5
AND ABS(S1.c2-S2.c2) <= 0.5
AND ABS(S1.c3-S2.c3) <= 0.5
AND ABS(S1.c4-S2.c4) <= 0.5

Unfortunately this query will not optimize well because of the join on a
calculated expression.

--
David Portas
SQL Server MVP
--

Displaying only a fixed number of Columns in Matrix

Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).

In other words.....

I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.

Thanks.

I did something like this. If you know that 6 columns will fill the width then you can return the (total amount of known columns / 6) as a page count.

Use the page count variable to hide/unhide a set of labels on top of the report. When a page label is clicked call the same report with the selected page as a parameter.

This will work if you can segment the result set based on a your page range in the database.

|||

Hmmm... That may not be exactly what I am looking for. Ideally, it would be nice if one can go into Group->Edit Column Group and set some property such as "# of Columns to Display for this Column Group" and behind the scenes it didn't care whether or not you bring in 6 columns from the DB or 600.

I hope there is a simpler solution or workaround to this.

|||Any body plz.. any ideas how to achieve this either in sql or through matrix formatting?