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 ChongSoftware Team Lead, ex-Business 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.
PortletPaulEE Topic AdvisorCommented:
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.)
awking00Information Technology SpecialistCommented:
This question cries out for some sample data and expected results and the dbms and version you are using. Please help us help you.
Busting 5 common myths about IT jobs.

Ignore popular stereotypes about what it’s like to work in IT. It’s a tech-driven world, and tech-based jobs are among the most diverse, and rewarding as you can get. Think you’ll be holed up in a basement, staring at a computer while outsourcing threatens your job security?

iBincAuthor Commented:
Need to close this. No longer an issue.
PortletPaulEE Topic AdvisorCommented:
Could you at least tell us what database this is for?
(nb: Previous questions prove useful to other readers)
iBincAuthor Commented:
Sure. I'd like to know for both sql server and oracle.
PortletPaulEE Topic AdvisorCommented:
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

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
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.