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
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

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


ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39526 Visits: 19991
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
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39526 Visits: 19991
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
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2119 Visits: 1820
SELECT distinct jNMBR, sNMBR FROM #myTable
except (select jNMBR, sNMBR from #myTable where cDate is null)
LHendren
LHendren
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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