I Want to Give Serial No in My Query According to Group

I want to give serial no in my Query according to Group

select tour_no, version_no ,itinerary_detail_no, row_number () over 
(partition by version_no order by itinerary_detail_no ) seq_no
from  itinerary_detail 
group by tour_no, version_no, itinerary_detail_no ;

UPDATE to assign serial numbers per group

UPDATE users u
SET    columntoupdate = g.increment
FROM  (
   SELECT u2.id
        , row_number() OVER (PARTITION BY u2.group_id ORDER BY u2.name) AS increment
   FROM   users u2
   ) g
WHERE u.id = g.id
-- AND u.columntoupdate IS DISTINCT FROM g.increment  -- ①
;

db<>fiddle here

No need to involve the table group at all.

You need to PARTITION BY group_id for serial number per group.

And join on the PK column.

① Add this WHERE clause to suppress empty updates (for repeated use). See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

Aside:

You are aware that this data structure is not easily sustainable? Names change, users are added and deleted, gap-less numbers per group are expensive to maintain - and typically unnecessary. See:

• Serial numbers per group of rows for compound key

Generate Row Serial Numbers in SQL Query

I'm not certain, based on your question if you want numbered rows that will remember their numbers even if the underlying data changes (and gives a different ordering), but if you just want numbered rows - that reset on a change in customer ID, then try using the Partition by clause of row_number()

row_number() over(partition by CustomerID order by CustomerID)

MySQL Join Query Autogenerate Serial Number

The reason you're skipping numbers is because of GROUP BY. Grouping is done after the serial numbers are generated, so it combines all the rows with the same cust_po and you only see one of the serial numbers.

Move the grouping into a subquery and add the serial numbers in the main query.

SELECT @a := @a+1 AS sno, t.*
FROM (SELECT @a := 0) AS a
CROSS JOIN (
    SELECT p.po_no as id,
            DATE_FORMAT(p.po_date, '%d-%m-%Y') as po_date,
            p.customer,
            p.cust_po as po_no,
            p.tot_ord_qty,
            DATE_FORMAT(p.delivery_date, '%d-%m-%Y') AS delivery_date,
            p.dc_status,
            p.inv_status,
            p.tot_dc_qty,
            p.tot_inv_qty,
            COALESCE(GROUP_CONCAT(distinct d.dc_no SEPARATOR ', '), 0) as dc,
            GROUP_CONCAT( d.active SEPARATOR ', ') as status
    FROM po_header AS p
    LEFT JOIN dc_details AS d ON p.cust_po = d.cust_po
    GROUP BY p.cust_po
    ORDER BY p.cust_po) AS t

Generate serial number in mysql query

Based on your reasons for not wanting to use user defined variables as wanting to avoid having 2 queries, one for inializing and one to use it you could use the following:

SELECT  @a:=@a+1 serial_number, 
        marks 
FROM    student_marks,
        (SELECT @a:= 0) AS a;

Query to assign serial number for rows without grouping together and without changing the order of rows

I am bit confused as you count trips even with 1 units, but if I understood the main objective right this should help.

drop table if exists test_table;
create table test_table as
select 254802 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units union
select 254803 as id, 0.5 as TripCount, 'Abdul' as DriverName,'2units' as units  union
select 254987 as id, 1 as TripCount, 'Abdul' as DriverName,'1units' as units  union
select 254910 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254911 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254912 as id, 1 as TripCount,'Abdul' as DriverName,'1units' as units  ;

select id,TripCount,DriverName,unit_flg,
sum (TripCount) over (partition by DriverName,unit_flg order by  id rows unbounded preceding )   cumulative_trips
from (select *,case when units='2units' then 1 else 0 end as unit_flg
from test_table ) a where a.unit_flg>0 group by 1,2,3,4;

how to insert a serial number through select query in postgresql?

If your s_id column is not already defined as serial or something (otherwise just leave it from the column list of your INSERT statement):

Are you searching for the row_number() window function? This creates an incrementing row count to your query starting with 1.

SELECT
    row_number() OVER () as s_id, -- <---------------
    person_id
    , subject_id,
    , MIN(exam_start_date) AS start_date
    , end_date 
    , COUNT(*) AS count
FROM main_table
GROUP BY person_id, subject_id, end_date 
ORDER BY person_id, subject_id

Maybe you should add the current maximum s_id from your actual students table if there are already some records to avoid conflicts with existing records with maybe same id values.

Serial numbers per group of rows for compound key

Don't. It has been tried many times and it's a pain.

Use a plain serial or IDENTITY column:

• Auto increment table column

CREATE TABLE address_history (
  address_history_id serial PRIMARY KEY
, person_id int NOT NULL REFERENCES people(id)
, created_at timestamp NOT NULL DEFAULT current_timestamp
, previous_address text
);

Use the window function row_number() to get serial numbers without gaps per person_id. You could persist a VIEW that you can use as drop-in replacement for your table in queries to have those numbers ready:

CREATE VIEW address_history_nr AS
SELECT *, row_number() OVER (PARTITION BY person_id
                             ORDER BY address_history_id) AS adr_nr
FROM   address_history;

See:

• Gap-less sequence where multiple transactions with multiple tables are involved

Or you might want to ORDER BY something else. Maybe created_at? Better created_at, address_history_id to break possible ties. Related answer:

• Column with alternate serials

Also, the data type you are looking for is timestamp or timestamptz, not datetime in Postgres:

• Ignoring time zones altogether in Rails and PostgreSQL

And you only need to store previous_address (or more details), not address, nor original_address. Both would be redundant in a sane data model.

Group BY serial number and select latest (last) created date for that record

you can use subquery to get the latest records for every serial_no. The result of the subquery is then join back on the original table so you can get the other columns.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT serial_no, MAX(created) max_date
            FROM    tableName
            GROUP BY serial_no
        ) b ON a.serial_no = b.serial_no AND
                a.created = b.max_date

• SQLFiddle Demo

---

Related Topics