8

SQL Selection of the maximum based on a major minor schema

 3 years ago
source link: https://www.codesd.com/item/sql-selection-of-the-maximum-based-on-a-major-minor-schema.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL Selection of the maximum based on a major minor schema

advertisements

I am trying to create a query that will select a DISTINCT line, select using a revision Minor / Major scheme. Below is an example table:

Serial Number | RevMajor | RevMinor
-----------------------------------
AQ155         |    1     |    1
AQ155         |    1     |    2
AQ155         |    1     |    1
AQ155         |    1     |    7
AQ155         |    2     |    1  <---------
JR2709        |    1     |    7
JR2709        |    2     |    2  <---------

How can I write a query in T-SQL 2008 that will select only the two highlighted lines, the "Newest Revision"?
Thanks in advance!

You could

select * from (
    select *, row_number() over (partition by [Serial Number] order by RevMajor desc, RevMinor desc) VersionRank
    from table
) T
where VersionRank = 1

Related Articles

Selection of the maximum based on the last Boolean value FALSE [T-SQL]

I would like to pull the last record by most current EndDate that IsOpen = FALSE" WeekID | EndDate | IsOpen | TransmitDate | 1 2015-03-14 False 2015-03-15 2 2015-03-20 False 2015-03-21 3 2015-03-26 True 2015-03-27 4 2015-04-02 True 2015-04-03 In this

SQL selection with the condition

I have a table like below, ID | Amt ------- 1 | $30 2 | $2 3 | $50 I want to do a select on the amount based on the ID and the thing is that ID=1 is not guarantee to be there. So, I want to check if ID=1 exist then I select the amount correspond to t

SQL - Selection in a table based on the corresponding values ​​given in another table

After having endlessly tried to handle large (3-35gb) csv files in R, I have moved over to SQL for handling these datasets. So I am using this code within an R environment (using the using the SQlite based RSQLite package) but it should not detract f

SQL Join only the maximum line puzzle

Given the following example data: Users +--------------------------------------------------+ | ID | First Name | Last Name | Network Identifier | +--------------------------------------------------+ | 1 | Billy | O'Neal | bro4 | +----+------------+--

SQL to query the maximum number of items available for a specific period (reservation system)

For a reservation system there is an inventory table and each item has a quantity (e.g. there are 20 chairs). Now the user can make a reservation for a specific period (e.g. 5 chairs for two hours "2010-11-23 15:00" - "2010-11-23 17:00"

The SQL query gets the maximum value on the date of occurrence

So we are doing some traffic reporting in our department. Therefore we got a table named traffic_report, which is build up like ╔════════════════╦═══════════╦═════════════════════╦═════════════╦═════════════╗ ║ hostname ║ interface ║ date_gmt ║ intra

In Oracle SQL, what is the maximum number of AND clauses in a query?

More of a curious question .. Studying a SQL and I want to know about what is the maximum number of AND clauses: WHERE condition1 AND condition2 AND condition3 AND condition4 ... AND condition? ... AND condition_n; i.e what isthe biggest possible n ?

SQL SELECT Returning the same value twice in GridView

Good Day All, So I'm working on a small application that stores entries into a table (author, short description of paper/book) and then metadata (user's vote, ID of entry, User ID, and comments). I have things working using JOIN's to get the informat

SQL Select from the table twice

I am trying to select from the same table twice within SQL. I have a POLICIES table that has an index (NEXTPOLICYID) that refers to itself. I need to compare the current premium with the estimated premium. How can I get a result that shows the follow

SQL query with the maximum value of the child table

Three pertinent tables: tracks (music tracks), users, and follows. The follows table is a many to many relationship relating users (followers) to users (followees). I'm looking for this as a final result: <track_id>, <user_id>, <most popula

Oracle SQL: Select Minimum and Maximum from different rows

I have a table which looks like this (max 2 rows per each Date-Item): Date Item Price ----------------------- 20140101 A 100 20140101 A 200 20140101 B 50 20140101 B 70 20140102 A 20 20140102 A 40 20140102 B 10 20140102 B 60 How can I have the maximum

SQL - Selection of the part of a string

If I have a simple table where the data is such that the rows contains strings like: /abc/123/gyh/tgf/345/6yh/5er In SQL, how can I select out the data between the 5th and 6th slash? Every row I have is simply data inside front-slashes, and I will on

SQL returns only the maximum date for each ID

I have a database with one table that looks like this Books(id, title, author_id, date) I am using type DATE for the date field. What the query is looking to do is return only the most recent book from each author, so even for the books that have the

KendoUI ComboBox selection of the Datasource-based option

I need help with this code. The code will get a JSON response from a PHP script and will create the ComboBox based on its content. This works... My problem is that I need to define the option that is selected by default, in this case, because the for

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK