TOP N by rows on SQL reproting services

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TOP N by rows on SQL reproting services

Author

Topic

ing_israel
Starting Member

3 Posts

Posted - 2008-04-12 : 18:16:42

Hi all!!! please help me!!!
I have a question about sorting by rows; I have the following table:
-------
Table1
-------
Date------code--ok-----nok---F1---F2---F3-----F56 (final)

08/04/2008 44---4023----78---2----0---40------0

08/04/2008 14---3023----58---2----0---40------0

08/04/2008 44---1023----28---2----0---40------0

08/04/2008 44---2023----18---2----0---40------0

08/04/2008 44---3023----08---2----0---40------0

The output must be from table1, the Fn equals zero is not on the final output and is sorted in descendent by max F on the current row:

Date------code---ok----nok----F3--F1

08/04/2008-44---4023---7840---40--2
08/04/2008-14---3023---58-----40--2

How can I achieve this output??? please look that the top 2 is from the rows only applies to Fn, the distinct must be for rows grouped by code, and the must important is to have the max(ok),
This DB comes from a PLC so we can have lots of duplicate data because the acquisition is every 30 minutes....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 02:45:46

It feels like what you're looking at is this:-

SELECT t.Date,t.code,t.ok,t.nok,t.F3,t.F1
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Date,code ORDER BY ok,nok DESC) AS RowNo,Date,code,ok,nok,F3,F1
FROM Table1
)t
WHERE t.RowNo =1

If its not giving you desired result,can you explain what your requirement is in detail

ing_israel
Starting Member

3 Posts

Posted - 2008-04-13 : 16:23:12

Thanks!!! but is not what I wish, I'm going to clarify my question:

Suppouse this output from my query (the query must select all records to analyze them):
table1
date--code-ok--nok--F1---F2--F3--F4--F5--F6--F7--F8--F9--F10
Abr---23---550-344--23---0---5---11--200-105-0---0---0---0

After this output I wish to sort Fn to get (is important first get all the records and at the end sort them and hidde all those contains zero):
result1 <--- here we have hidde the Fn who contains zero and Fn is sorted by TOP N descending... so How can I achieve it???
date--code-ok--nok--F5---F6---F1---F4
Abr---23---550-344--200--105--23--11

The query must be something like:
SELECT DISTINCT t.date,t.code,Max(t.ok) AS MaxOk,t.nok,t.f1,t.f2,t.f3,t.f4,t.f5,t.f6,t.f7,t.f8,t.f9,t.f10 from table1 t ORDER BY MaxOk DESC;
But I need to add the TOP N by Fn, to get the result1 table....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 23:30:31

The columns with 0 values can be made hidden by providing an expression for visibility in reporting services. just give IIF(Me.Value =0,"true","false") for hidden property of the column.
What does N indicate. Is it the total number of records in your result set or is it number of records which are to be retrieved whose value user inputs?

ing_israel
Starting Member

3 Posts

Posted - 2008-04-16 : 08:23:53

Excelent idea, I will stop looking for an SQL answer I will program the request on SSRS 2005, Thankss for your attention,
This issue is to complex to achieve on SQL, the sorting is another huge problem...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources