Analytic Query to do a Min on a column and return the entire row

Need some sql that will get the min on a column and return the entire row without using a subquery or a group by... basically an analytic sql statement that doesn't require as subquery to return the lowest column row data based upon a particular column. Like a group by but not using a group by or a subquery.

thanks
iBincAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
it depends on how your raw data looks like, a subquery (or multiple sub queries) sometimes cannot be avoided.
0
PortletPaulfreelancerCommented:
A "generic" answer might to use MIN(...) OVER(PARTITION BY ...)

e.g.

select t.*
       , MIN(t.somecolumn) OVER(PARTITION BY t.code, t.foreign_key) as min_in_group
from sometable t


But I don't know which database or version you are using and not all do support this syntax.

When asking question related to "SQL" it is necessary to also identify the database vendor. This is because different "dialects" of SQL are used by each vendor. To avoid proposing queries that won't work in your database, please let us know in advance what database you use please.

There are topics for most database platforms to choose from and these topics also help experts locate new questions that might answer.

Not it is definitely worth noting the database version you use in the question too.  
(For example; MySQL versions prior to version 8 would not support the OVER() clause.)
0
awking00Commented:
This question cries out for some sample data and expected results and the dbms and version you are using. Please help us help you.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

iBincAuthor Commented:
Need to close this. No longer an issue.
0
PortletPaulfreelancerCommented:
Could you at least tell us what database this is for?
(nb: Previous questions prove useful to other readers)
0
iBincAuthor Commented:
Sure. I'd like to know for both sql server and oracle.
0
PortletPaulfreelancerCommented:
Both Oracle and SQL Server support MIN() OVER()

Let's say I want rows from a table with the minimum val2 & it is important that I get the whole rows's original values &/OR I need to calculate the difference between the minimum and some value on each row

test setup
CREATE TABLE Table1
    ([code] varchar(1), [val1] int, [val2] int)
;
    
INSERT INTO Table1
    ([code], [val1], [val2])
VALUES
    ('A', 1, 555),
    ('A', 3, 784),
    ('A', 2, 267)
;

Open in new window

queries:
select
      t.*
    , min(val2) over(partition by code) min_val2
from table1 t
;

select
*
from (
      select
            t.*
          , min(val2) over(partition by code) min_val2
      from table1 t
      ) d
where val2 = min_val2
;

Open in new window

Note that within the OVER clause the PARTITION BY is similar to the columns you might otherwise have used in a group by clause

results:
| code | val1 | val2 | min_val2 |
|------|------|------|----------|
|    A |    1 |  555 |      267 |
|    A |    3 |  784 |      267 |
|    A |    2 |  267 |      267 |
        
| code | val1 | val2 | min_val2 |
|------|------|------|----------|
|    A |    2 |  267 |      267 |

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Hmmm... No subqueries ?

select top 1 * from Table1
order by min(val2) over (partition by code order by val2,val1)

-- or for multiple "code" entries

select t.*
from Table1 t
cross apply (select distinct code,min(val2) over (partition by code order by val2,val1) from Table1) v(code,val2)
where t.code = v.code
and t.val2 = v.val2
2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.