A T-sql question.

Question

A T-sql question.

Dan121

SSCommitted

Points: 1616
More actions
June 3, 2015 at 11:16 am

#304708

TranId NameDate
111John 1/3/2015
111Mike4/4/2015
222Mike5/5/2015
222John1/6/2015
333kim3/7/2015
333Kim2/8/2015
I need all order no. associate with above columns (with exact 5 rows).
example:
elect TranId , Name, Date , [OrderNo] from CUSTOMER ....
Can anyone help?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Dan121 (6/3/2015)
TranId NameDate
111John 1/3/2015
111Mike4/4/2015
222Mike5/5/2015
222John1/6/2015
333kim3/7/2015
333Kim2/8/2015
I need all order no. associate with above columns (with exact 5 rows).
example:
elect TranId , Name, Date , [OrderNo] from CUSTOMER ....
Can anyone help?

Note the link in my signature for the best way to get help. This does not give us enough to go by.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63506 More actions · Answer 2

Welcome to SSC. Since you're new, I'll fix up your post to show you a way to do it that will make the job of those trying to help easier.

-- create table

CREATE TABLE trans (

TranID INT,

UserName VARCHAR(4),

TranDate DATE

);

GO

-- populate table with data...

INSERT INTO trans(tranID,Username,TranDate) VALUES

(111,'John', '1/3/2015'),

(111,'Mike','4/4/2015'),

(222,'Mike','5/5/2015'),

(222,'John','1/6/2015'),

(333,'Kim','3/7/2015'),

(333,'Kim','2/8/2015');

Which 5 rows? Any? you could use TOP... Look up TOP in Books Online (BOL)... hidden carefully under the F1 key. There is a TON of stuff there that explains how SQL works.

Dan121 SSCommitted Points: 1616 More actions · Answer 3

Thanks pietlinden for formatting the query. The table already exists with OrderNo. and more columns. I need to retrieve OrderNo along with TranId, Name, Date.

TranId Name Date [OrderNo.]

111 John 1/3/2015 ####

111 Mike 4/4/2015 ####

222 Mike 5/5/2015 ####

222 John 1/6/2015 ####

333 kim 3/7/2015 ####

333 Kim 2/8/2015 ####

I can do:

select [OrderNo] from CUSTOMER where Tranid = 111 and Name = 'John' and date = '1/3/2015'

or select [OrderNo] from CUSTOMER where Tranid = 111 and Name = 'mike' and date = '4/4/2015'

or select [OrderNo] from CUSTOMER where Tranid = 222 and Name = 'mike' and date = '5/5/2015'

and on.....

is there is best and fast way to do especially when I need to retrieve thousands of rows?

Please don't go by 'Normalization'. I have a similar task.

pietlinden SSC Guru Points: 63506 More actions · Answer 4

The table doesn't exist for the rest of us, though. That's why you post it. To get tested code.

What 5 records are you trying to return? You never answered that.

Dan121 SSCommitted Points: 1616 More actions · Answer 5

I need to find:

1. OrderNo. for 111 John 1/3/2015

2. OrderNo. for 111 Mike 4/4/2015

3. OrderNo. for 222 Mike 5/5/2015

4. OrderNo. for 222 John 1/6/2015

5. OrderNo. for 333 kim 3/7/2015

Dan121 SSCommitted Points: 1616 More actions · Answer 6

Is there alternative (best) solution for this? ( database AdeventureWorks, table Sales.Customer). In my case, StoreID, AccountNumber and rowguid have duplicate values.

select * from Sales.Customer where

StoreID = 934 and AccountNumber = 'AW00000001' and rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'

or

StoreID = 928 and AccountNumber = 'AW00000010' and rowguid = 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'

or

StoreID = 1252 and AccountNumber = 'AW00000100' and rowguid = '7D37485D-A4CF-4C08-8D3C-364DEEC7C841'

or

StoreID = 1186 and AccountNumber = 'AW00000200' and rowguid = '7C96C878-A038-4B2F-825F-016C922D6407'

or

StoreID = 386 and AccountNumber = 'AW00000300' and rowguid = '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

From what table are we to pull the order number? You have only provided half the needed information.

Dan121 SSCommitted Points: 1616 More actions · Answer 8

Please forget about previous ones.

My question is:

Is there alternative (best) solution for this? ( database AdeventureWorks, table Sales.Customer). In my case, StoreID, AccountNumber and rowguid have duplicate values.

select * from Sales.Customer where

StoreID = 934 and AccountNumber = 'AW00000001' and rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'

or

StoreID = 928 and AccountNumber = 'AW00000010' and rowguid = 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'

or

StoreID = 1252 and AccountNumber = 'AW00000100' and rowguid = '7D37485D-A4CF-4C08-8D3C-364DEEC7C841'

or

StoreID = 1186 and AccountNumber = 'AW00000200' and rowguid = '7C96C878-A038-4B2F-825F-016C922D6407'

or

StoreID = 386 and AccountNumber = 'AW00000300' and rowguid = '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Dan121 (6/3/2015)
Please forget about previous ones.
My question is:
Is there alternative (best) solution for this? ( database AdeventureWorks, table Sales.Customer). In my case, StoreID, AccountNumber and rowguid have duplicate values.
select * from Sales.Customer where
StoreID = 934 and AccountNumber = 'AW00000001' and rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'
or
StoreID = 928 and AccountNumber = 'AW00000010' and rowguid = 'CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD'
or
StoreID = 1252 and AccountNumber = 'AW00000100' and rowguid = '7D37485D-A4CF-4C08-8D3C-364DEEC7C841'
or
StoreID = 1186 and AccountNumber = 'AW00000200' and rowguid = '7C96C878-A038-4B2F-825F-016C922D6407'
or
StoreID = 386 and AccountNumber = 'AW00000300' and rowguid = '7256F9B7-5253-4384-B8DE-60B6F0ADF5A6'

I have no idea what you are trying to accomplish. Why not ask the question you are trying to answer and provide the necessary DDL (CREATE TABLE) statement(s), sample data (INSERT INTO statements) for table(s) involved, and the expected results based on the sample data so we can provide you with tested code in return?

SQL aus HH SSC Eights! Points: 833 More actions · Answer 10

The other ones are right...

What is that what you wanna know from the forum?

from that what i've read actually... no, there is no better way just

... oder by TransID ASC

corresponding to the create/insert before:

SELECT TOP (5) TranID, UserName, TranDate FROM trans ORDER BY TranID ASC, TranDate ASC

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 11

Jason A. Long

SSC-Insane

Points: 23786

June 4, 2015 at 6:35 am

#1802956

Edit... I didn't like the solution I posted...

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

Jason A. Long (6/4/2015)
Edit... I didn't like the solution I posted...

Solution to what? 😀

^{“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

sgmunson SSC Guru Points: 110640 More actions · Answer 13

I'm going to go out on a limb on the basis of an early post by the original poster, and offer this:

DECLARE @trans AS TABLE (

TranID INT,

UserName VARCHAR(4),

TranDate DATE

);

INSERT INTO @trans (tranID,Username,TranDate) VALUES

(111,'John', '1/3/2015'),

(111,'Mike','4/4/2015'),

(222,'Mike','5/5/2015'),

(222,'John','1/6/2015'),

(333,'Kim','3/7/2015'),

(333,'Kim','2/8/2015');

SELECT T.*, C.OrderNo

FROM @trans AS T

INNER JOIN CUSTOMER AS C

ON T.UserName = C.Name

AND T.TranID = C.Tranid

AND T.TranDate = C.[date];

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 14

ChrisM@Work (6/4/2015)
Jason A. Long (6/4/2015)
Edit... I didn't like the solution I posted...
Solution to what? 😀

It was just a rewrite of the original code Sales.Customer code.

I didn't like it... So I killed it. 😀