| |
| Nieuwe functies in SQL 2005 - ROW_NUMBER
|
|
|
Location: Blogs Ferry Onderwater - Developer |
 |
| Posted by: Ferry Onderwater |
7-2-2006 0:00 |
Vóór SQL 2005 was het erg lastig om bijvoorbeeld het volgende te doen: Geef mij het 11e tot en met het 20e record uit een subset (voor paging of iets dergelijks). Of: gooi alle records die aan een criteria doen weg behalve de nieuwste 10. In dit korte item laat ik zien aan de hand van de laatste vraag hoe je dit in SQL 2005 kunt doen.
De uitdaging is om van een rij records alles behalve de nieuwste 'x' (stel 9) weg te gooien.
In SQL 2005 zit de functie 'ROW_NUMBER'.
ROW_NUMBER heeft altijd een parameter nodig, namelijk hoe en waaraan de rijnummers moeten worden toegekend. In ons geval willen we de data niet verder splitsen (partitioneren) maar 'gewoon' nummers toekennen aan de rijen in een bepaalde volgorde. Dit kunnen we doen door als parameter (ORDER BY ) te geven.
Dus met dat in het achterhoofd schreef ik de volgende code:
Code:
DELETE FROM tblHistory WHERE ROW_NUMBER() OVER (ORDER BY Created DESC) >= 10
Dat werkt dus niet  Je krijgt gelijk de foutmelding 'Windowed functions can only appear in the SELECT or ORDER BY clauses.' om je oren.
Het blijkt dat we ROW_NUMBER alleen kunnen gebruiken in een SELECT-statement óf in een ORDER BY.
Gelukkig heeft SQL 2005 nóg een stukje nieuwe functionaliteit, WITH - AS.
Met WITH AS kunnen we een soort virtuele tabel maken. Microsoft noemt het een 'temporary named result set', of ook een 'common table expression'. Deze vullen we met een query, dus gewoon een SELECT. En dáár kan zoals we net gezien hebben wél de ROW_NUMBER functionaliteit gebruikt worden.
Op deze virtuele tabel mogen we SELECT, INSERT, UPDATE en DELETE statements loslaten. Als we deze informatie bij elkaar plaatsen kunnen we de volgende code creeeren:
Code:
WITH OrderedHistory AS (SELECT ROW_NUMBER() OVER(ORDER BY Created DESC) AS RowNumber, guid FROM tblHistory)
DELETE FROM OrderedHistory WHERE RowNumber >= 10
Veel plezier!
|
| Copyright ©2006 Ferry Onderwater |
| Permalink |
Trackback |
Comments (9)
Add Comment
|
Betr: Nieuwe functies in SQL 2005 - ROW_NUMBER |
By Stan Evers on
3-2-2009 16:26 |
Precies wat ik zocht! Dank voor de suggestie
Groet,
Stan |
|
|
Coach Handbags |
By Coach Handbags on
16-7-2010 7:34 |
utopia
With Coach Handbags of styles and colors to choose from you really can’t go wrong.The heels collection provides everything from the big hit of the season ankle boots, to strappy Nike Air Jordan.After hitting the market in 2004, Tory Burch Cheap Gucci Shoes have become a fashion essential for those looking for the latest in style and surprisingly comfort. Michael Jordan Shoescome in a wide range of variety and styles. The big portion of their market and nike dunk appeal comes from their collection of flats that not only is trendy but is known for their wear ability and comfort.With a wide range of designers and Nike Air Jordan on the market, these fashionista savvy Cheap MBT Shoes can come off the cover of Elle magazine without breaking the bank. |
|
|
nothing is impossible··· |
By anlia on
26-7-2010 2:53 |
Thanks for your sharing, good job!
Are you interested in the gucci outlet series? Topguccishoes.com will be the right place that you have come to. There are many different kinds products of discount gucci shoes for your selection, including gucci handbags, gucci sneakers which content with gucci men shoes, gucci jeans, Gucci Skirt, gucci bikini , gucci belts, Gucci Spicy Underwear, Gucci Wallets, Gucci Jewelry, gucci hats, gucci sunglasses and Women Gucci boots line. Welcome to the discount gucci handbags as well as ugg bailey button boots and buy gucci with the best quality at affordable price. |
|
|
shi |
By fiwedding on
30-7-2010 3:55 |
supply in stock and custom lace front wigs, full lace wigs, lace wigs, human hair wigs,
remy lace front wigs, cheap wigs, cheap, buy, celebrity
full lace wigs
lace wigs
lace wigs sale
lace front wigs
synthetic front lace wigs
A Famous Dresses Shop which sell directly Wedding Dresses, Evening Dress, Bridesmaid Dresses,Prom dresses
cheap wedding dresses
cheap evening dresses
cheap prom dresses
cheap evening dresses
cheap prom dresses
Elegant evening dresses are always associated with the brides and their bridesmaids.Shopping for evening dresses and your wedding dress in stylish bridal ..
|
|
|
mens timberland boots |
By timberland 6 inch on
1-8-2010 14:19 |
| One Sunday womens timberland boots morning, I was told that someone was waiting for me in timberland 6 boots office. The young person who answered the rectory timberland boots door said that it was "the woman who said she left all the notes." When I saw her mens timberland boots I was shocked, since I immediately recognized her from church but had no timberland boots outlet idea that it was she who wrote the notes. She was sitting in a chair in timberland roll top boots office with her hands folded in her lap. Her head was bowed and when she raised white timberland boots it to look at me, she could barely smile without pain. Her face was disfigured, and timberland classic boat skin so tight from surgical procedures that smiling or laughing was very difficult for her timberland kids waterproof boots . She had suffered terribly from treatment to remove the growths timberland shoes that had so marred her face. We chatted for a while that Sunday morning and agreed to meet for timberland womens premium boots lunch later that week. As it turned out we went to lunch several timberland boots women times, and she always wore a hat during the meal. I think that treatments of some timberland shoe company sort had caused a lot of her hair to fall out. We shared things about our lives. I told her about my women timberland shoes schooling and growing up. She told me that she had worked for timberland 6 inch years for an insurance company. She never mentioned family, and I did not ask. I have thought about her timberland mens custom often over the years and how she struggled in a society that places an incredible premium on looks, class, wealth and all the other fineries of life. She suffered from a disfigurement that cannot be made to look attractive. I know that her condition black timberland boots hurt her deeply. |
|
|
Betr: Nieuwe functies in SQL 2005 - ROW_NUMBER |
By grth on
3-8-2010 9:15 |
As the sun set over the Timberland Shoes at this year's Cannes Timberland Boots Festival, Timberland shoes us one Timberland Shoes on sale was being talked about Wholesale Timberland Shoes in the glamorous Timberland Shoes Outlet and watering places - and the Discount Timberland shoes hasn't even Discount Vibram FiveFingers been made Timberland Boots on sale yet. Fought Discount Vibram Five Fingers over by three Wholesale Timberland Boots rival companies, the Vibram FiveFingers hottest property is the Timberland Boots Outlet life story Vibram FiveFingers on Sale of a Vibram Five Fingers long-dead couturiere Discount Timberland Boots.
Born out of MBT Shoes, brought up MBT Shoes us a church Wholesale MBT Shoes, hers was an Discount MBT Shoes astonishing achievement. She MBT Sandals designed the world's most celebrated MBT Shoes on Sale perfume - famously Vibram Five Fingers Shoes worn by Marilyn Monroe (with nothing else) MBT Shoes Outlet in bed - and Vibram Five Fingers little pink Vibram Five Fingers on sale suit worn by visvim slippersJackie Kennedy Gucci Shoes on the day her MBT Sandals on sale President husband was Gucci Shoes on sale assassinated. She Vibram FiveFingers outlet invented those Prada Shoes prerequisites of modern Birkenstock Slippers style: costume jewelry Prada Shoes on sale, the Little Black Dress, the shoulder Discount Gucci Shoes and even MBT Sandals outlet the suntan. Discount Vibram Five Fingers The first of Discount Prada Shoes the must-have designers, she has Gucci Shoes Outlet also had the most Vibram Five Fingers outlet staying power. She dressed Prada Shoes Outlet the aristocracy in the 1920s, discount MBT Sandals film stars in the Thirties and Vibram FiveFingers Shoes debutantes in the Fifties.
And now waiting to Replica Chanel Bags snap up her Chanel Bags signature quilted handbags, chain belts Coach Bags and two-tone Louis Vuitton Bags. She is Gabrielle Chanel Wallets, better known Replica Coach Bags as Coco Coach Wallets, and she had Replica Gucci Bags the most fertile Louis Vuitton Wallets imagination ever to Prada Bags inhabit the world of fashion Jimmy Choo Bags. Her rags-to-riches story Gucci Bags has it all: Replica Prada Bags glamour, money, Tous Bags, aristocratic suitors, Chanel Handbags even a treacherous Replica Louis Vuitton Bags liaison with a German Nazi Coach Handbags officer in occupied Paris. Coco Louis Vuitton Handbags, one of the Fake Chanel Bags most beautiful girls Prada Handbags in France, Fake Coach Bags was notoriously Jimmy Choo Handbags and a determined Gucci Handbags. She initially made her Tous Handbags in the world as a Chanel Bags on sale and, even when she was established as a formidable Coach Bags on sale, she was Coach outlet happy Louis Vuitton Bags on sale to be the Fake Gucci Bags mistress of the Chanel Wallets on sale Duke of Coach outlet us, the richest Coach Wallets on sale in England.
Even Louis Vuitton Wallets on sale today, you can Prada Bags on sale still see her Fake Prada Bags famous entwined CC logo painted Jimmy Choo Bags on sale in gold on the lampposts of the Gucci Bags on sale current Duke's estate Tous Bags on sale in Mayfair, London - in memory Chanel Bags outlet of her legendary Fake Louis Vuitton Bags romance with his Coach Bags outlet besotted predecessor. It all Louis Vuitton Bags outlet started in Saumur in France's Chanel Wallets outlet Valley, where Gabrielle Bonheur Coach Wallets outlet was born to a sickly Louis Vuitton Wallets outlet in 1883 and given Prada Bags outlet the name of the nun who Jimmy Choo Bags outlet delivered her. Her mother Gucci Bags outlet died when she was Tous Bags outlet six, and when her wine merchant Discount Chanel Bags father walked out on the Discount Coach Bags family, she was sent to an Discount Louis Vuitton Bags orphanage. Gabrielle Discount Chanel Wallets proved a rebellious Discount Coach Wallets child who spent most Discount Louis Vuitton Wallets of her time with her nose Discount Prada Bags in romantic novels Discount Jimmy Choo Bags, planning to escape the Discount Gucci Bags drudgery of her peasant Discount Tous Bags background. But she was Wholesale Branded Bags also taught to sew and Wholesale Branded Wallets make hats. At 17, she became a shop Wholesale Branded Handbags assistant in a ladies' outfitters.
She was already a remarkable UGG Boots on sale beauty with a swan Black UGG Boots neck and an insouciance that Sage UGG Boots made her stand out from the Tomato Red UGG Boots crowd. Tall and tomboyish, UGG Sandals she was determined to Navy blue UGG Boots succeed - at anything Baked Clay UGG Boots - and, for a while, she sang UGG Boots 5815 in the Trocadero nightclub UGG Boots 5825 where she earned UGG Boots 5879 her nickname from UGG Classic Short Boots one of her favourite UGG Classic Tall Boots songs, Coco. She was UGG Argyle Knit Boots nearly 20 when she caught UGG Bailey Button Boots the eye of a dashing UGG Classic Cardy Boots cavalry officer from the local UGG Classic Mini Boots garrison who seduced UGG Hazel Sandals her and took her under the wing Wholesale UGG Boots of his older mistress in Paris.
If Coco was ghd Straighteners compliant in her new Discount ghd Straighteners role, she ghd Pretty in Pink was far from ghd Straighteners on sale subservient. She always ghd Precious Gift Set justified this ghd Straighteners outlet louche period by ghd Purple Gift Set declaring she found ghd Hair Straighteners society women Discount ghd Hair Straighteners boring and ghd Radiance Set courtesans much more ghd Hair Straighteners on sale dramatic with ghd Hair Straighteners outlet their heavy ghd IV Stylers make-up and Discount ghd IV Stylers hour-glass figures.
But Coco ghd IV Stylers on sale herself refused to ghd IV Stylers outlet dress like these ladies ghd outlet of the night ghd IV Salon Styler in their ghd on sale long, over-embellished frocks Wholesale ghd Straighteners that dragged in the Ray Ban Sunglasses and Armani Sunglasses lover, Arthur Capel, lent Oakley Sunglasses her the money to Chanel Sunglasses open her Dior Sunglasses first Paris Gucci Sunglasses shop near the Ritz, where Chanel still has its glamorous Police Sunglasses headquarters. By D&G Sunglasses 1916
So she bought cheap MBT VOI Shoes from department stores and Chanel Jewelry made plain dresses out of Tiffany Jewelry material, which, at the time Juicy Jewelry, was used only Cartier Jewelry for men's underclothes. Predictably, her Louis Vuitton Jewelry work was considered BVLGARI Jewelry scandalous. Even her short Chanel Jewelry on sale hairstyle was in response to necessity. One Tiffany Jewelry on sale night, her Juicy Jewelry on sale gas heater blew up, singeing Cartier Jewelry on sale her long locks Louis Vuitton Jewelry on sale. Her first thought BVLGARI Jewelry on sale was to cancel Chanel Jewelry outlet her evening's engagement Tiffany Jewelry outlet. But then she took a Juicy Jewelry outlet pair of scissors and gamely lopped Cartier Jewelry outlet a foot off her Louis Vuitton Jewelry outlet damaged tresses BVLGARI Jewelry outlet; she put a hat Chanel Bangles on and went with her Tiffany Bangles beau to the opera. Hats Juicy Bangles were to be Coco's Cartier Bangles stepping stone Louis Vuitton Bangles to sartorial greatness BVLGARI Bangles. Soon, she persuaded her rich lover Chanel Bracelets to set her up with a Tiffany Bracelets hat shop in the Juicy Bracelets provinces. Then, when he introduced her Cartier Bracelets to a powerful English Louis Vuitton Bracelets industrialist with dashing BVLGARI Bracelets film star looks called Chanel Earrings Arthur "Boy" Capel, she Tiffany Earrings spotted her opportunity. Chanel, now 26, threw Juicy Earrings herself at him, following him to Cartier Earrings Paris on the night sleeper. When he Louis Vuitton Earrings succumbed, she shamelessly played off her BVLGARI Earrings two lovers against each other Chanel Necklaces - even convincing one to buy her Tiffany Necklaces a Paris flat, Juicy Necklaces while the other Cartier Necklaces was persuaded to Louis Vuitton Necklaces open a bank account for her BVLGARI Necklaces. And all the while she Chanel Key Chains was inventing new Coach Key Chains fashions. One day it Gucci Key Chains was so cold at Louis Vuitton Key Chains the Deauville races Chanel Sweater Necklaces that she put on D&G Sweater Necklaces her Englishman Juicy Sweater Necklaces lover's sweater, tying it Wholesale Branded Jewelry at the waist.
Coco was Timberland Schuhe in her element. Timberland ブーツ She started Coach Taschen Twenties' flapper Coach バッグ fashion with its Chanel Taschen swinging pearls, Chanel バッグ short skirts and Louis Vuitton Taschen sleeveless dresses, Louis Vuitton バッグ all Prada Taschen perfect Prada バッグ for the UGG Stiefel modern woman UGG ブーツl with an ghd Haarglätter active lifestyle. And she MBT Schuhe designed the Little Black Chanel Schmuck Dress, livening Chanel ジュエリー it up with Tiffany Schmuck costume jewelry Tiffany ジュエリー - another Ray Ban Sonnenbrillen first. Soon, she Ray Ban サングラス began to attract jealous Timberland обувь criticism. Denouncing the Little Black Coach CYMKN Dress, New York society Chanel CYMKN hostess Elsa Maxwell Louis Vuitton CYMKN snarled: "She can't be Prada CYMKN in mourning for "Boy" UGG Сапоги Capel, she wasn't married to ghd для выпрямления волос him. So she's making MBT обувь the whole world wear Chanel ювелирные изделия black instead."
Coco ignored Tiffany ювелирные изделия the taunt and Ray Ban Солнцезащитные очки, in 1923, came up Timberland chaussures with one of her Coach Sacs biggest triumphs Chanel Sacs: Chanel No. 5. By now Coco Louis Vuitton Sacs was so famous Prada Sacs that the richest men UGG bottes in the world ghd défrisage des cheveux were vying MBT chaussures for her favours Chanel bijoux and, although Tiffany bijoux wealthy in her Ray Ban Lunettes de soleil own right, she Timberland zapatos vowed to make Coach bolsas life complete by marrying Chanel bolsas the second Duke of Louis Vuitton bolsas Westminster - known as Bend'Or after Prada bolsas his grandfather's chestnut UGG botas Derby winner which ghd alisadores de pelo he was said to MBT zapatos resemble. She had Chanel joyería met him on a trip Tiffany joyería to Monte Carlo Ray Ban gafas de sol. The twice-divorced Duke Timberland sapatos - a gambler and womanizer Coach Sacos - was enchanted. Coco Chanel Sacos, who was one of Louis Vuitton Sacos the first to popularise Prada Sacos the Riviera as a holiday UGG botas destination and ghd alisadores de cabelo to revel in a suntan MBT sapatos as a symbol of the Chanel jóias leisured life, Tiffany jóias entertained him at her Ray Ban óculos de sol villa in Monaco Timberland scarpe. She wore Timberland 구두 her Coach Borse new invention: Coach 가방 white Chanel Borse satin pyjamas. Chanel가방 In return, he Louis Vuitton Borse took Louis Vuitton 가방 her Prada Borse to his Prada가방 English UGG stivali country UGG 부츠 estate ghd Stiranti per capelli, where MBT 구두 he grew MBT scarpe orchids and Chanel gioielli daisies Chanel보석류 for her Tiffany gioielli. Riding with hounds Tiffany 보석류 and demonstrating a talent Ray Ban Occhiali da sole for fly-fishing Ray Ban색안경 and tennis.
|
|
|
|
|
|
|
|
|
|
|
|
|