Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Abbreviated SELECT FROM output Expand / Collapse
Author
Message
Posted Thursday, August 28, 2014 7:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:25 AM
Points: 10, Visits: 72
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




Post #1608264
Posted Thursday, August 28, 2014 7:39 AM This worked for the OP Answer marked as solution
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:08 PM
Points: 293, Visits: 647
SELECT distinct jNMBR, sNMBR FROM #myTable
WHERE cDate IS NOT NULL

Post #1608278
Posted Thursday, August 28, 2014 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1608305
Posted Thursday, August 28, 2014 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:25 AM
Points: 10, Visits: 72
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)

Post #1608318
Posted Thursday, August 28, 2014 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1608319
Posted Thursday, August 28, 2014 12:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:25 AM
Points: 10, Visits: 72
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.
Post #1608371
Posted Thursday, August 28, 2014 12:44 PM This worked for the OP Answer marked as solution


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 1,425, Visits: 1,685
SELECT distinct jNMBR, sNMBR FROM #myTable
except (select jNMBR, sNMBR from #myTable where cDate is null)
Post #1608385
Posted Thursday, August 28, 2014 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:25 AM
Points: 10, Visits: 72
Thank all of you. It works perfectly.

The EXCEPT is a nice spin (changed to IS NULL) on the cDate field.
Post #1608415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse