Solved

SQL Query Using 1 column to group by with multiple columns in query

Posted on 2016-11-10
6
25 Views
Last Modified: 2016-11-10
I have the following view that I am trying to group by the column called fHEATNO but also show the other columns in the view with it. But it's telling me that the other fields are not part of an aggregate function. How can I group this by the fHEATNO column and still show the others? If there are multiple fHEATNO values then I can use the first record of those with multiple records with the same fHEATNO.


SELECT     TOP (100) PERCENT LTRIM(RTRIM(fHEATNO)) AS HEATNo, FSUFFIX, FVENDNO, FCOMPANY, FELEMC, FELEMMN, FELEMSI, FELEMP, FELEMS, FELEMCR, FELEMNI, 
                      FELEMMO, FELEMCU, FELEMCBTA, FELEMTI, FELEMCO, FELEMAL, FELEMSE, FELEMSN, FELEMV, FELEMN, FELEMB, FYIELDSTR1, FTENSILE1, FELONG1, 
                      FREDUC1, FSHIPHARD1, FHARDENABLE1, FMICROSTRUC1, FMACROTECH1, FGRSIZE1, FULTRASON1, FYIELDSTR2, FTENSILE2, FELONG2, FREDUC2, 
                      FSHIPHARD2, FHARDENABLE2, FMICROSTRUC2, FMACROTECH2, FGRSIZE2, FULTRASON2, RTRIM(FCAPABILITY) AS FCAPABILITY, FMILL, FCREATEDATE, 
                      FCOMMENTS, FPARTNO, FMELTTEMP, FHTTRTCOND, FCHANGEDATE, FMELTSOURCE, FCOUNTRY, FHEATLOT, FOVLOT, FCUSTHEATNO
FROM         dbo.HEATDATA
WHERE     (fHEATNO <> '')
GROUP BY fHEATNO
ORDER BY fHEATNO

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41882997
The error message is telling you that GROUP BY must contain EVERY column that isn't using an aggregate function such as SUM() COUNT() MIN() MAX() etc.

This is by design and isn't a fault.

To achieve what I think you want needs a different technique, and I suggest ROW_NUMBER() for this. HOWEVER I will be guessing some of the detail.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41883007
Not that familiar with ROW_NUMBER. There are some fHEATNO values that are the same along with almost every other field except for the fpartno field. That would be the only one that would be different and I really don't need that field anyways. So how could I achieve to group these using the ROW_NUMBER?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41883014
You need to consider what conditions form "the first row". I have chosen a date column in descending order (so you get the "most recent" row) but as I don't know your data at all this is a guess:
SELECT
        *
FROM (SELECT
                LTRIM(RTRIM(fHEATNO))                                 AS HEATNo
              , ROW_NUMBER() OVER (PARTITION BY LTRIM(RTRIM(fHEATNO)) 
\                                  ORDER BY FCHANGEDATE DESC)         AS rn
              , FSUFFIX
              , FVENDNO
              , FCOMPANY
              , FELEMC
              , FELEMMN
              , FELEMSI
              , FELEMP
              , FELEMS
              , FELEMCR
              , FELEMNI
              , FELEMMO
              , FELEMCU
              , FELEMCBTA
              , FELEMTI
              , FELEMCO
              , FELEMAL
              , FELEMSE
              , FELEMSN
              , FELEMV
              , FELEMN
              , FELEMB
              , FYIELDSTR1
              , FTENSILE1
              , FELONG1
              , FREDUC1
              , FSHIPHARD1
              , FHARDENABLE1
              , FMICROSTRUC1
              , FMACROTECH1
              , FGRSIZE1
              , FULTRASON1
              , FYIELDSTR2
              , FTENSILE2
              , FELONG2
              , FREDUC2
              , FSHIPHARD2
              , FHARDENABLE2
              , FMICROSTRUC2
              , FMACROTECH2
              , FGRSIZE2
              , FULTRASON2
              , RTRIM(FCAPABILITY) AS FCAPABILITY
              , FMILL
              , FCREATEDATE
              , FCOMMENTS
              , FPARTNO
              , FMELTTEMP
              , FHTTRTCOND
              , FCHANGEDATE
              , FMELTSOURCE
              , FCOUNTRY
              , FHEATLOT
              , FOVLOT
              , FCUSTHEATNO
        FROM dbo.HEATDATA
        WHERE (fHEATNO <> '')
    ) AS D


WHERE RN = 1

Open in new window

Please note I do NOT recommend using ORDER BY in views, you should only apply an order by in the final select query. Using order in views is a performance problem.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41883028
The date column will work fine as the first record for the duplicates. I will test that out and post back shortly. I didn't know that about the ORDER BY in a view. Can that really impact the performance in a noticeable way?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41883034
Think about the ORDER BY this way....

select *
from atable t
inner join aview v on a.id = v.fk
where t.x = 'y' and v.cdate >= '20160101'
order by t.something, v.someother

any order by in that view is wasted effort because
1. you are joining to it
2. you filter out many rows
3. end up with a different order anyway
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41883039
That makes sense. I'll have to go through all my views and remove the ORDER BY. Thank you for that insight! Appreciate it. And your query syntax works exactly how I wanted it to. Thank you very much!!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

747 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

11 Experts available now in Live!

Get 1:1 Help Now