Abbreviated SELECT FROM output

Question

Post reply

Abbreviated SELECT FROM output

LHendren

SSC Veteran

Points: 282
More actions
August 28, 2014 at 7:13 am

#290172

I remember learning how to do this years ago, but cannot remember. I would like the data to be returned like this:
jNMBR sNMBR
7545 1000
8000 1001
8000 1002
8011 1003
9578 1004
rather than like this:
jNMBR sNMBR
7545 1000
7545 1000
7545 1000
7545 1000
8000 1001
8000 1001
8000 1002
8011 1003
8011 1003
8011 1003
9578 1004
9578 1004
9578 1004
Here is the data information:
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #myTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
cDate DATETIME,
sNMBR INT,
jNMBR varchar(5),
tNMBR varchar(3)
)
SET DATEFORMAT DMY
SET IDENTITY_INSERT #myTable ON
INSERT INTO #mytable
(ID, cDate, sNMBR, jNMBR, tNMBR)
SELECT '1','Oct 17 2013 12:00AM',1000,'7545','001' UNION ALL
SELECT '2','Oct 17 2013 12:00AM',1000,'7545','008' UNION ALL
SELECT '3','Oct 18 2013 12:00AM',1000,'7545','011' UNION ALL
SELECT '4','Oct 19 2013 12:00AM',1000,'7545','002' UNION ALL
SELECT '5','Oct 17 2013 12:00AM',1001,'8000','010' UNION ALL
SELECT '6','Oct 20 2013 12:00AM',1001,'8000','009' UNION ALL
SELECT '7','Oct 17 2013 12:00AM',1002,'8000','070' UNION ALL
SELECT '8',NULL,1002,'8000','110' UNION ALL
SELECT '9','Oct 17 2013 12:00AM',1003,'8011','080' UNION ALL
SELECT '10','Oct 15 2013 12:00AM',1003,'8011','011' UNION ALL
SELECT '11','Oct 17 2013 12:00AM',1003,'8011','002' UNION ALL
SELECT '12','Oct 11 2013 12:00AM',1004,'9578','001' UNION ALL
SELECT '13','Oct 12 2013 12:00AM',1004,'9578','194' UNION ALL
SELECT '14','Oct 13 2013 12:00AM',1004,'9578','070' UNION ALL
SELECT '15',NULL,1004,'9578','010'
SET IDENTITY_INSERT #mytable OFF
SELECT jNMBR, sNMBR FROM #myTable
WHERE cDate IS NOT NULL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rxm119528 SSCrazy Points: 2960 More actions · Answer 1

SELECT distinct jNMBR, sNMBR FROM #myTable

WHERE cDate IS NOT NULL

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Two other commonly-used methods:

SELECT jNMBR, sNMBR

FROM #myTable

WHERE cDate IS NOT NULL

GROUP BY jNMBR, sNMBR

SELECT jNMBR, sNMBR

FROM (

SELECT jNMBR, sNMBR, rn = ROW_NUMBER() OVER(PARTITION BY jNMBR, sNMBR ORDER BY (SELECT NULL))

FROM #myTable

WHERE cDate IS NOT NULL

) d

WHERE rn = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LHendren SSC Veteran Points: 282 More actions · Answer 3

Thanks to both of you. All three solutions work for my query.

Taking this a step further, what if I do not want to see the jNMBR and sNMBR at all when cDate is NULL? In other words, this would be the result:

jNMBR sNMBR

7545 1000

8000 1001

8011 1003

9578 1004

(the jNMBR 8000 sNMBR 1002 row is not shown because cDate is NULL)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

LHendren (8/28/2014)
Thanks to both of you. All three solutions work for my query.
Taking this a step further, what if I do not want to see the jNMBR and sNMBR at all when cDate is NULL? In other words, this would be the result:
jNMBR sNMBR
7545 1000
8000 1001
8011 1003
9578 1004
(the jNMBR 8000 sNMBR 1002 row is not shown because cDate is NULL)

Do you mean "where any cDate is NULL in the set where jNMBR = 8000 and sNMBR = 1002"?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LHendren SSC Veteran Points: 282 More actions · Answer 5

Yes, "where any cDate is NULL in the set where jNMBR = 8000 and sNMBR = 1002" or for any jNMBR/sNMBR association (besides 8000 and 1002) that has a NULL cDate whereby I do not want it shown.

Thank you.

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 6

SELECT distinct jNMBR, sNMBR FROM #myTable

except (select jNMBR, sNMBR from #myTable where cDate is null)

LHendren SSC Veteran Points: 282 More actions · Answer 7

Thank all of you. It works perfectly.

The EXCEPT is a nice spin (changed to IS NULL) on the cDate field.