September 5, 2013 at 4:21 am
I a table that stores item data. The tables contains an itemid, label code, date time and recordID. Each item has various names associated with it. These names are stored in another table. I basically need to create a query that selects only one of the many names for each item. I have provided some sample data to illustrate.
-- CREATE TABLES
CREATE TABLE ITEM
(
[ITEMID] NVARCHAR(20) NOT NULL,
[LabelCode] NVARCHAR(2) NOT NULL,
[DateStamp] [datetime] NOT NULL,
[RECID] BigINT NOT NULL,
PRIMARY KEY CLUSTERED
(
[ITEMID] ASC,
[RECID] ASC
) ON [PRIMARY]
)ON [PRIMARY]
CREATE TABLE AltNames
(
[ID] INT IDENTITY(1,1) NOT NULL,
[NAME] NVARCHAR(10) NOT NULL,
[DateStamp] [datetime] NOT NULL,
[RECID] BigINT NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON[PRIMARY]
)ON [PRIMARY]
--Insert some test data
INSERT INTO ITEM
([ITEMID],
[LabelCode],
[DateStamp],
[RECID])
SELECT 'W-004663633','RA',GETDATE(),5914152711
UNION ALL
SELECT 'W-005945839','RA',GETDATE(),5914152712
UNION ALL
SELECT 'W-005945840','RA',GETDATE(),5914152713
UNION ALL
SELECT 'W-005945841','RA',GETDATE(),5914152714
INSERT INTO AltNames
(
[NAME],
[DateStamp],
[RECID]
)
SELECT 'THOMAS',GETDATE(),5914152711
UNION ALL
SELECT 'NIJLANDS',GETDATE(),5914152711
UNION ALL
SELECT 'EVANS',GETDATE(),5914152711
UNION ALL
SELECT 'BRIDGES',GETDATE(),5914152712
UNION ALL
SELECT 'KEITH',GETDATE(),5914152712
UNION ALL
SELECT 'ARNO',GETDATE(),5914152713
UNION ALL
SELECT 'HAVERKOORT',GETDATE(),5914152713
UNION ALL
SELECT 'SOMORE',GETDATE(),5914152713
UNION ALL
SELECT 'OMOREGIE',GETDATE(),5914152714
UNION ALL
SELECT 'SKYWALKER',GETDATE(),5914152714
SELECT
A.[ITEMID]
, A.[LabelCode]
,B.NAME
FROM AltNames B INNER JOIN TableA A
ON A.RECID = B.RECID
My output is required to look like:
ITEMID LABELCODE NAME
W-004663633RATHOMAS
W-005945839RAKEITH
W-005945840RASOMORE
W-005945841RASKYWALKER
September 5, 2013 at 4:42 am
On what criteria do you select those 4 rows?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 5, 2013 at 4:49 am
Oh sorry. The criteria is a join on The recid and then pull out The name.
September 5, 2013 at 4:51 am
That's not what Koen meant but never mind it appears that you're choosing the name based on descending alphabetical order.
Try the below:
SELECT
A.[ITEMID]
, A.[LabelCode]
,(select top 1 NAME
from AltNames as b
where a.RECID = b.RECID
order by NAME desc)
FROM ITEM as a
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 5, 2013 at 5:52 am
Thanks for your response. That is just what I wanted.
September 6, 2013 at 8:15 am
Abu Dina (9/5/2013)
That's not what Koen meant but never mind it appears that you're choosing the name based on descending alphabetical order.Try the below:
SELECTA.[ITEMID]
, A.[LabelCode]
,(select top 1 NAME
from AltNames as b
where a.RECID = b.RECID
order by NAME desc)
FROM ITEM as a
or this one:
SELECT [ITEMID]
, [LabelCode]
,NAME
FROM
(
SELECT
A.[ITEMID]
, A.[LabelCode]
,B.NAME
,row_number() OVER (PARTITION BY A.ITEMID ORDER BY B.NAME DESC) as rn
FROM AltNames B INNER JOIN ITEM A
ON A.RECID = B.RECID
) abc
WHERE rn=1
September 6, 2013 at 4:29 pm
This too,
WITH cte AS (
SELECT a.LabelCode,
b.NAME,
RANK() OVER(PARTITION BY a.ITEMID ORDER BY b.NAME DESC) AS Ranking
FROM ITEM a JOIN AltNames b ON b.RECID = a.RECID)
SELECT * FROM cte WHERE Ranking = '1'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply