Database

Mycroft Holmes20-Apr-12 13:40

Mycroft Holmes

20-Apr-12 13:40

Create a sub query that uses Row_Number() and Partition based in the id field in scheme_opdetm, selecting data from your transaction table and numbered/ordered by the date field. Join that to your main query and filter it on row_number = 1.

Never underestimate the power of human stupidity
RAH

Richard.Berry10020-Apr-12 20:51

Richard.Berry100

20-Apr-12 20:51

Thanks Mycroft, but I have no idea how to do this.

First, I'm not sure how to put in the PARTITION clause,
Second, the Despatch date is in the header table (opheadm), with a Sales Order Num, but I want to Partition by Product not by Sales Order Num, and the product is in the Details Table (opdetm).

This what I have tried so far, but I get an error "The column 'order_no' was specified multiple times for ordlist

SELECT opdetm.warehouse, opdetm.product, opdetm.net_price, opdetm.despatched_qty, opheadm.date_despatched 
FROM vektron.scheme.opdetm INNER JOIN vektron.scheme.opheadm ON opdetm.order_no = opheadm.order_no ,
(SELECT ROW_NUMBER() OVER (PARTITION BY opheadm.order_num ORDER BY date_entered DESC) , *  FROM vektron.scheme.opheadm inner join vektron.scheme.opdetm on opdetm.order_no = opheadm.order_no) as ordlist
WHERE(((opheadm.status) = '8') And ((opdetm.warehouse) = '06') And ((opdetm.net_price) > 0) And ((opdetm.despatched_qty) > 0)) and ordlist.order_no = 1 and opdetm.product = ordlist.product
ORDER BY opdetm.product, opheadm.date_despatched DESC

Mycroft Holmes20-Apr-12 21:23

Mycroft Holmes

20-Apr-12 21:23

Without the DB I'm not going to be able to build the query, but here is how I would go about it.

Create a query that returns you the minimum bits you need to do the partition job. That should be the ID, the date and the product fields. The ID to link back to your result set, the data to order by and the product to partition over.

Once you have that right you put it aside and create another query that had the ID field and all the bits you want in your end result.

Now join the 2 queries!

Never underestimate the power of human stupidity
RAH

Jörgen Andersson21-Apr-12 1:18

Jörgen Andersson

21-Apr-12 1:18

Try something like this:

SQL

WITH original_query AS (
    SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
    FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
    WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
    ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
    )
,last_despatched AS (
    SELECT  product,Max(date_despatched) AS date_despatched
    FROM    original_query
    GROUP BY product
    )
SELECT  o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM    original_query o,last_despatched l
WHERE   o.product = l.product
AND     o.date_despatched = l.date_despatched

I love CTEs.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Richard.Berry10021-Apr-12 2:41

Richard.Berry100

21-Apr-12 2:41

Thanks so much Jorgen!

That worked brilliantly.

I did have to remove:

ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;

becaue it gave an error (MSSQL 2008 R2): "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Jörgen Andersson21-Apr-12 6:44

Jörgen Andersson

21-Apr-12 6:44

Glad to help.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: mysql to ms sql server

vvashishta23-Apr-12 19:16

vvashishta

23-Apr-12 19:16

SELECT
b.RowNumber,
iSrno,
vUserCode,
dLoggedDate
FROM (
SELECT
iSrno,
vUserCode,
dLoggedDate,
ROW_NUMBER() OVER ( PARTITION BY vUserCode ORDER BY vUserCode DESC ) 'RowNumber'
FROM HOUserLog) b
WHERE RowNumber = 1

/*
Here HOUserLog is the table from where the Group By MAX needs to be pick.
ROw_Number() OVER ( PARTITION BY .... ) will generate the RowNumber according to the Group By
Column you provided in ORDER BY ..... DESC Clause.
Now pick the values for every RowNumber Column = 1 will get you the solution.

MARK AS ANSWER IF WORKS

- Happy Coding -
Vishal Vashishta

SSRS 2005 question

SQL Ed20-Apr-12 10:13

SQL Ed

20-Apr-12 10:13

After running a report in SSRS 2005 I am trying to export it into excel and I am getting an error
"Index is out of range. Must be non-negative and less than the size of the collection. Parameter name:index"

When I remove the "Page break at start" or the "Page break at end" grouping property, it fixes the problem, but I need this property on because that is how the report gets seperated onto seperate Excel worksheets by the groups I have set up. Any suggestions??? D'Oh! | :doh:

mysql to ms sql server

Ramkumar_S19-Apr-12 21:27

Ramkumar_S

19-Apr-12 21:27

Hi All,

I have same table structure in Mysql and sqlserver.
If there is change in Mysql table, then sqlserver table should get updated.

Please suggest proper way to do.

We dont have SSIS we only use ssms2005 express.

Ramkumar
("When you build bridges you can keep crossing them. ")

http://ramkumarishere.blogspot.com

Eddy Vluggen20-Apr-12 0:48

Eddy Vluggen

20-Apr-12 0:48

Ramkumar_S wrote:
Please suggest proper way to do.

Sql Express doesn't offer this functionality out of the box. You'd have several options;

Add the MySql server as a linked server and sync using triggers
Write an app to do the synchronization, using the Sync Framework
Upgrade Sql Server

Bastard Programmer from Hell Suspicious | :suss:

Re: mysql to ms sql server

jschell20-Apr-12 10:27

jschell

20-Apr-12 10:27

I suspect the solution would require that you first analyze what happens if there is an error.

For example you update the MySQL table and at that moment the SQL Server database is down so of course the corresponding table cannot be updated then - so what do you expect to happen then?

Additionally how soon does the data need to be in the SQL Server database?

Getting unique combination of rows in SQL??

Lima318-Apr-12 21:08

18-Apr-12 21:08

I have a requirement as in below

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))

    insert into @TEST values ('A','B'),('C','D'),('E','F'),('B','A'),('D','C'),('F','E')

SELECT A.* FROM @TEST A, @TEST B WHERE A.X = B.y

gives
X Y

A B
C D
E F
B A
D C
F E

But I have to get only the combination (AB or BA) ,(CD or DC),(EF or FE) in my select statement. In my sql we have some thing called DistinctRow.. What is it in SQL ..Pelase help ..urgent..

AHSAN11118-Apr-12 21:31

AHSAN111

18-Apr-12 21:31

Try using the Distinct Keyword

SQL

SELECT Distinct(A.x), A.Y FROM @TEST A, @TEST B WHERE A.X = B.y

Blue_Boy19-Apr-12 0:57

Blue_Boy

19-Apr-12 0:57

here it is

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))
 
insert into @TEST values ('A','B')
insert into @TEST values ('C','D')
insert into @TEST values ('E','F')
insert into @TEST values ('B','A')
insert into @TEST values ('D','C')
insert into @TEST values ('F','E')


SELECT A.X
,(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) as subY

 FROM @TEST A
where 
(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) is not null
order by a.x

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Lima319-Apr-12 1:53

19-Apr-12 1:53

Excellent .. thanks for the reply..

Lima319-Apr-12 1:56

19-Apr-12 1:56

I was looking for some key word like distinctrow in MYSQL.. dont we have any thing like that in SQL.

Lima319-Apr-12 2:38

19-Apr-12 2:38

Finally SELECT X,Y FROM @TEST WHERE X