Give integer value to column based on sort

• gideon.e

  SSC Veteran

  Points: 273

  More actions

  November 22, 2018 at 7:49 am

  #382329

  I want to write a sql statement to achieve the following and need some help.
  I need to give a sequential integer (starting from 1) to a column in the database table (column called Prioriteit) based on a sort that is as follows :

  First column kultivar (i.e. string Ascending), then GroepperingsGroep (i.e. string Ascending), Then LaasteOntledingsdatum (i.e. date oldest to newest) and lastly Laastesuiker (i.e. real largest to smallest). According to this sort integer values should be given to Prioriteit for each GroepperingsGroep. I am attaching a picture to explain the end result needed
  

  Regards

• Thom A

  SSC Guru

  Points: 99130

  More actions

  November 22, 2018 at 8:05 am

  #2014341

  Have you had a look at ROW_NUMBER? Specifically, you need to make sure you at your PARTITION and ORDER BY expressions. I suggest having a look at the function, and having a go yourself. If you get stuck, reply with your attempt, along with some consumable sample data (images really aren't heflpful for the users here, as we can't make use of the data), and we can help you further by showing where you may have gone wrong.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  November 22, 2018 at 8:39 am

  #2014343

  There's a nice style update statement you can use:
  ;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY kultivar,GroepperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit,
           *
      FROM myTable
)
UPDATE CTE
   SET CTE.Prioriteit = CTE.NewPrioriteit

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  November 22, 2018 at 6:41 pm

  #2014369

  gideon.e - Thursday, November 22, 2018 7:49 AM

  I want to write a sql statement to achieve the following and need some help.
  I need to give a sequential integer (starting from 1) to a column in the database table (column called Prioriteit) based on a sort that is as follows :

  First column kultivar (i.e. string Ascending), then GroepperingsGroep (i.e. string Ascending), Then LaasteOntledingsdatum (i.e. date oldest to newest) and lastly Laastesuiker (i.e. real largest to smallest). According to this sort integer values should be given to Prioriteit for each GroepperingsGroep. I am attaching a picture to explain the end result needed
  

  Regards

  There is currently a television ad here in the United States that features two guys sitting on a sofa. One of them asked the other if he knows a good tradesmen for some job on his house. The second actor says "I got somebody I used" . In this first actor immediately demands that his friend check out all the guys references, get initial bids provide all of the license numbers, etc. The ad is for an online service that recommends tradespeople and their pitch is that you should use it because you ain't got time to do all this as a favor for a friend.

  Your failure to post DDL is like the rude actor in this television ad. We now have to transcribe and correct everything you posted. People here are working for free, and yet you expect them to be your secretary and transcribe the raw undefined data from your silly pictures.

  You don't seem to know that the only date display format allowed in ANSI/ISO standard SQL is based on ISO 8601 so you use a local dialect. We have no idea what the key is. We have no idea what the datatypes are. We cannot see any constraints on any of the columns. I bet you didn't bother to read the posting rules for the forum.

  Thom A. give you some good advice about the row_number () construct. ,

  Please post DDL and follow ANSI/ISO standards when asking for help.