SQL query for getting few things from one table

sql select from multiple tables
sql query examples from multiple tables
sql select where
limit in sql server
sql query to retrieve data from database
sql limit offset
sql join
sql count

I have a problem with one table. This is how table looks:

+----------+------------+-------------+------+
| index_id | version_id |    date     | type |
+----------+------------+-------------+------+
|        1 |          4 | today       | C    |
|        1 |          2 | last month  | C    |
|        1 |          4 | today       | U    |
|        2 |          3 | yesterday   | c    |
|        2 |          4 | today       | C    |
|        2 |          2 | last year   | U    |
|        3 |          7 | yesterday   | C    |
|        3 |          6 | last month  | C    |
|        3 |          8 | today       | U    |
+----------+------------+-------------+------+

What I want to achieve is to get indexes (index_id) with max version and max date of both types.

Like this:

+----------+------------+-----------+------+
| index_id | version_id |   date    | type |
+----------+------------+-----------+------+
|        1 |          4 | today     | C    |
|        1 |          4 | today     | U    |
|        2 |          4 | today     | C    |
|        2 |          3 | yesterday | U    |
|        3 |          7 | yesterday | C    |
|        3 |          8 | today     | U    |
+----------+------------+-----------+------+

I want to ask you for some ideas for this problem. Thanks.

I would go for row_number():

select t.*
from (select t.*,
             row_number() over (partition by index_id, type order by version_id desc) as seqnum
      from t
     ) t
where seqnum = 1;

SQL SELECT TOP, LIMIT, ROWNUM, The SELECT TOP clause is useful on large tables with thousands of records. MySQL supports the LIMIT clause to select a limited number of records, while  select A.* from table_A A where A.id in ( select B.id from table_B B where B.tag = 'chair' ) Alternatively you could join the tables and filter the rows you want: select A.* from table_A A inner join table_B B on A.id = B.id where B.tag = 'chair' You should profile both and see which is faster on your dataset.

Use row_number and partition to get the max in each group for a given index_id and type combination

select index_id,version_id,date,type from 
(
    select *, row_number() over (partition by index_id,type order by version_id,desc) as r_no
    from table
) a
where a.r_no = 1

SQL SELECT Statement, The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. SELECT Syntax. SELECT  When you work with databases, querying data from a table is one of the most common tasks that you have to deal with on a regular basis. To query data from one or more tables, you use the SQL SELECT statement. SQL SELECT statement syntax The following illustrates the SQL SELECT statement syntax:

I would use subquery :

select t.*
from table t
where version_id = (select max(t1.version_id) 
                    from table t1 
                    where t1.index_id = t.index_id and 
                          t1.type = t.type 
                   ); 

SQL Server SELECT, A) SQL Server SELECT – retrieve some columns of a table example. The following query finds the first name and last name of all customers: SELECT first_name,  To query data from a table, you use the SQL SELECT statement. The SELECT statement contains the syntax for selecting columns, selecting rows, grouping data, joining tables, and performing simple calculations. The SELECT statement is one of the most complex commands in SQL, therefore, in this tutorial, we’ll focus on the basics only.

Table Joins, A "Join" can be recognized in a SQL SELECT statement if it has more than one two new tables, let's say you would like to select the customer's name, and items doesn't work with yours, please check your specific database documentation. if not then you would need to cast the string dates to a date time, assuming Event_Time_UTC is a date/time datatype. a left join lets us return all records from the 1st table and only those that match from the 2nd. the t1.* returns only the columns from table1.

SQL joins and how to use them, When our data was all in a single table, we could easily retrieve a particular SQL handles queries across more than one table through the use of JOINs. all do slightly different things, but the basic theory behind them all is the same. To join one table to another, PostgreSQL needs to know several pieces of information:. Subqueries, or subselect statements, are a way to use a result set as a resource in a query. These are often used to limit or refine results rather than run multiple queries or manipulate the data in your application. With a subquery, you can reference tables to determine inclusion of data or, in some cases,

Splitting data into related tables (article), There is one big thing to realize about these tables: they are describing When the same data is replicated across multiple tables, there can be If we do that, we need to make sure we have a way to relate the data across the tables, which we'll get to later. More efficient SQL with query planning and optimization. Just use the table name: SELECT myTable.*, otherTable.foo, otherTable.bar That would select all columns from myTable and columns foo and bar from otherTable.

Comments
  • "max version and max date" what does this mean? One, the other, a combination of both?
  • How do you get 2 | 3 | yesterday | U? It's not in the source. There is only 2 | 3 | yesterday | c.
  • Today isnt a date, at least isnt one for oracle
  • Which Oracle version are you using?
  • Not fast enough to beat @Gordon-Linoff to the answer Sigh :(
  • Once you partition by index_id, you dont need order by index_id. Because all the rows on the partition has the same id