Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2016-11-10
6
Medium Priority
?
69 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 50

Expert Comment

by:Paul
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 50

Accepted Solution

by:
Paul earned 2000 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 50

Expert Comment

by:Paul
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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

601 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