cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yoshihirok
Memorable Member
Memorable Member

How to get max value of rows by SQL Server - Get Rows

I want to get max value of rows by SQL Server - Get Rows.1.png

 

My scenario:

Get max value of rows in table of SQL Server.

Query image In T-SQL language, select max(value) from table.

 

Current document:

'Get rows' action have no document about Options example.

https://docs.microsoft.com/ja-jp/Connectors/sql/#get_rows

 

'Get Rows' action have some options, like 'Aggregation', 'Order by', etc.

 

Regards,

Yoshihiro Kawabata

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @yoshihirok,

 

Thanks for the update.

If you mean translate the 

Select Max(ColumnName) as MaxAmount

From SQL-Table

into the $Apply code, then the code should be:

aggregate(ColumnName with max as MaxAmount)

 

I thought you mean to aggregate the count of rows, that should be not available, as it is documented, the $count is not allowed to work with an aggregation method. (refer 3.1.5 Virtual Property $Count )

 

Again, if I have any misunderstandings, please let me know.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-micsh-msft
Community Support
Community Support

Hi @yoshihirok,

 

I don't think that would be possible.

The Fitler Query under the SQL Server Get-Rows action obey the OData query rules, and based on what I know, there is no OData function available to get the Max value of the rows in a SQL table, through the OData query.

For the Query options under OData, please refer:

URL Conventions

Search for Query options.

 

Besides, if you just want to get the Last row of the SQL Table, we could take use of Last() or first() function defined in WDL, with a Compose Action.

 

Please post back if I have any misunderstnadings.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-micsh-msft

 

thank you for your reply.

and OData query 4.0 seems to have aggregate like max.

 

"7.2 Aggregation Methods
The client may specify one of the predefined aggregation methods min, max, sum, average, and countdi..."

 

and SQL Server - Get rows action have parameters like 'Aggregation transformation'.

1.png

 

 

 

The reason is my question, How to get max value of rows by SQL Server.

 

I will start to learn OData query.

 

Regards,

Yoshihiro Kawabata

 

Hi @yoshihirok,

 

Thanks for the update.

If you mean translate the 

Select Max(ColumnName) as MaxAmount

From SQL-Table

into the $Apply code, then the code should be:

aggregate(ColumnName with max as MaxAmount)

 

I thought you mean to aggregate the count of rows, that should be not available, as it is documented, the $count is not allowed to work with an aggregation method. (refer 3.1.5 Virtual Property $Count )

 

Again, if I have any misunderstandings, please let me know.

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-micsh-msft

 

I will try the aggregate(ColumnName with max as MaxAmount).

 

Now, I use 'Get rows' with order by, and a 'Compose' with expression.1.png

 

 

 

1. Action - SQL Server - Get rows

  Name = MaxNo

  Order by = No desc

  Top Count = 1

  Select Query = No

2. Action - Compose

  Input = outputs('MaxNo').body?.value[0].No

 

Next, I will try 'aggregate transformation' parameter for getting max No.

 

Regards,

Yoshihiro Kawabata

I post a Flow cookbook by Order by = No desc, and Top Count = 1 without aggregate

 

"Notify Max No of SQL Server records to Phone
https://powerusers.microsoft.com/t5/Flow-Cookbook/Notify-Max-No-of-SQL-Server-records-to-Phone/m-p/4...

 1.png

 

 

 

 

Regards,

Yoshihiro Kawabata

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (3,758)