SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Abbreviated SELECT FROM output


Abbreviated SELECT FROM output

Author
Message
LHendren
LHendren
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 80
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
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 804
SELECT distinct jNMBR, sNMBR FROM #myTable
WHERE cDate IS NOT NULL


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16810 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
LHendren
LHendren
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 80
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16810 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
LHendren
LHendren
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 80
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
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1717 Visits: 1820
SELECT distinct jNMBR, sNMBR FROM #myTable
except (select jNMBR, sNMBR from #myTable where cDate is null)
LHendren
LHendren
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 80
Thank all of you. It works perfectly.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search