Solved

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

Posted on 2016-11-10
6
47 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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