Solved

Can I have help with some T-SQL syntax?

Posted on 2014-11-12
8
72 Views
Last Modified: 2014-12-02
Can someone help me with a T-SQL query I am trying to create. I am sure it will all look very simple when it is resolved, but after a couple of hours on my own I still have not cracked it.

Simplified, I have a table with say 4 fields, Code, Name1, Name2, Name3
The same "Code" value might appear in several records because it may have different "Names" associated with it. This means the table might have 20,000 different records, but there are perhaps only 10,000 distinct values for "Code".
(Not my data - it comes from Amazon.)

I would like to list each "unique" value of "Code", and am happy to see the "Name" fields from the first (any) record for that "distinct" value of "Code".

I have tried things like the following. I know this fails (and I am combining WHERE with ON) but it hopefully gives an idea of what I am trying to achieve :

SELECT AMZ1.Code AS 'CODE', AMZ2.*
FROM VIEW_AmazonDataGroupedByCode AS AMZ1
LEFT OUTER JOIN (SELECT TOP 1 *
                 FROM TABLE_AmazonData AS AMZ3
                 WHERE AMZ3.Code = AMZ1.Code) AS AMZ2 ON AMZ2.Code = AMZ1.Code

Can anyone suggest how I read the additional fields from the first (any) record containing a distinct "Code"?

Many thanks.
0
Comment
Question by:colinasad
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40437517
Can you give some sample records of your table and the desired output for those records? It's not entirely clear to me what you're trying to achieve.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437527
Try this. I don't know what the difference between VIEW_AmazonDataGroupedByCode and TABLE_AmazonData, so I've only used TABLE_AmazonCode.

With MyTable as
(Select Code, [Name], ROW_NUMBER() OVER(PARTITION BY Code ORDER BY [Name]) as MyRow
FROM TABLE_AmazonData)
Select Code, [Name]
From MyTable
Where MyRow = 1

Open in new window

0
 

Author Comment

by:colinasad
ID: 40437578
Thanks for the fast responses.

Just a quick reply from me :
My "VIEW" is an already created SQL_Server View that gives me a simple list of the distinct "Code" values from the underlying TABLE.
I was hoping to use the output from that VIEW to then find further details from the TABLE, for the first (any) record with a matching "Code".

I will now try working with your suggestion, Phillip.
Thanks.
0
 

Author Comment

by:colinasad
ID: 40437626
Hi Phillip,
Using SQL Server Management Studio Express, I can get your syntax to work perfectly for me when I execute it from a Query pane.
However, when I paste the same syntax into a new "View" in my SQL Server database and try to save it, I get a "Visual Studio Just-In-Time Debugger" error message and the SQL SMSE program crashes and closes.
Is there something about your T-SQL syntax that prohibits it from being the source of a SQL Server "View"?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437640
Don't believe so. It certainly shouldn't make SSMS crash!

Maybe you need a repair on your SSMS.
0
 

Accepted Solution

by:
colinasad earned 0 total points
ID: 40437926
Hi Phillip,
Whenever I try to save the "OVER (PARTITION ..." type of syntax into a new "View" I always get the "Just-In-Time Debugger" error message; whatever the cause.

I have therefore solved my problem with some less elegant syntax, but at least it does not crash. What I have done is :

SELECT AZ0.Code AS 'CODE',
(SELECT TOP 1 Name1
               FROM TABLE_AmazonData AS AZ1
               WHERE AZ1.Code= AZ0.Code) AS 'NAME1',
(SELECT TOP 1 Name2
               FROM TABLE_AmazonData AS AZ2
               WHERE AZ2.Code= AZ0.Code) AS 'NAME2',
(SELECT TOP 1 Name3
               FROM TABLE_AmazonData AS AZ3
               WHERE AZ3.Code= AZ0.Code) AS 'NAME3',
(SELECT TOP 1 Name4
               FROM TABLE_AmazonData AS AZ4
               WHERE AZ4.Code= AZ0.Code) AS 'NAME4',
FROM VIEW_AmazonDataGroupedByCode AS AZ0

As I say, I know this is less elegant than your suggestion and probably executes less efficiently, but it works!

Any comments?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437943
No - if it works, why change it!
0
 

Author Closing Comment

by:colinasad
ID: 40475521
Other suggested solutions caused a debugger message on my system. I created a less elegant, but workable, solution of my own.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now