Access First() function

• hugo_17_23 19942

  Valued Member

  Points: 57

  More actions

  October 10, 2012 at 5:35 pm

  #282998

  hi im migratting an MS Access database to SQL Server 2008, and I have a trouble trying to convert the first function from Access to T-SQL in this query:

  Select Pay_table.register,First(Pay_table.type),First(Pay_table.key),

  First(Pay_table.Idleacv1),Pay_table.n_row,Pay_table.n_exercise,

  Pay_table.c_n_period,First(Pay_table.fperceh1),

  First(Pay_table.d_hpresentation),First(Pay_table.tdiepco1),

  First(Pay_table.f_previous_pay),First(Pay_table.gain),

  First(Pay_table.lost),First(Pay_table.c_key_det)

  From Pay_table

  Group by Pay_table.register,Pay_table.n_row,Pay_table.n_exercise,

  Pay_table.c_n_period

  Order by Pay_table.register,

  Pay_table.n_exercise,First(Pay_table.fperceh1) Desc,

  First(Pay_table.d_hpresentation) Desc;

  An example of the data type of the columns is this

  register type key Idleacv1 n_row n_exercise

  AAAAA111111 F 40012-601513 40012-601513-4 21 2011

  c_n_period fperceh1 d_hpresentation tdiepco1 f_previous_pay

  1 31/01/2011 6:15:00 am 1 18/07/2011

  gain lost c_key_det

  1632 10016 851536

  Can Anyone help me please?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 10, 2012 at 8:32 pm

  #1547857

  First of all, welcome to SQL Server Central,

  I can see you're new around here and might not be aware of the best way to get help. I recommend you to take a look at the article linked on my signature and please, don't post the same question in several forums, that won't help.

  As I'm not sure on how your data works, I'll give you a possible solution that might not be the best for you. If you give us DDL and Sample data in a consumable format, it will be better to help you.

  I'm not aware of a "first" function, but you can simulate it with CTEs and ROW_NUMBER.

  Here's the example:

  WITH CTE AS(

  SELECT Pay_table.register

  ,Pay_table.type

  ,Pay_table.KEY

  ,Pay_table.Idleacv1

  ,Pay_table.n_row

  ,Pay_table.n_exercise

  ,Pay_table.c_n_period

  ,Pay_table.fperceh1

  ,Pay_table.d_hpresentation

  ,Pay_table.tdiepco1

  ,Pay_table.f_previous_pay

  ,Pay_table.gain

  ,Pay_table.lost

  ,Pay_table.c_key_det

  ,ROWNUMBER() OVER( PARTITION BY register, n_row, n_exercise, c_n_period

  ORDER BY fperceh1 DESC, d_hpresentation DESC) AS rn

  FROM Pay_table)

  SELECT register

  ,type

  ,KEY

  ,Idleacv1

  ,n_row

  ,n_exercise

  ,c_n_period

  ,fperceh1

  ,d_hpresentation

  ,tdiepco1

  ,f_previous_pay

  ,gain

  ,lost

  ,c_key_det

  FROM CTE

  WHERE rn = 1

  Seems a lot of code but there are reasons on why SQL Server experts don't use terms as first or last.

  If you have questions, feel free to ask.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2